Hi all,
Greetings.
I have a subreport in a report with volume and price. I would like to get the total of the top 2 volume * price based on the usage. Sample data and required result is as follows:
Period volume Price Rank vol*Price
1 100 7 6
2 200 8 5
3 400 9 2 3600
4 300 8 3
5 500 9 1 4500
6 250 7 4
Total 5100
I need only the highest and second highest volume and I need the total (5,100 in the above example) to be used in further calculations.
I created formula called rank in which I used formulas to calculate the maximum (volume) and nthlargest(2, volume) to identify the two highest volumes and calculated the vol * Price. But I am not able to insert a summary to sum the two values (and also not able to do a running total of the vol*price field.
Any help on how I can achieve this is very highly appreciated.
Thanks and regards,
TR
Greetings.
I have a subreport in a report with volume and price. I would like to get the total of the top 2 volume * price based on the usage. Sample data and required result is as follows:
Period volume Price Rank vol*Price
1 100 7 6
2 200 8 5
3 400 9 2 3600
4 300 8 3
5 500 9 1 4500
6 250 7 4
Total 5100
I need only the highest and second highest volume and I need the total (5,100 in the above example) to be used in further calculations.
I created formula called rank in which I used formulas to calculate the maximum (volume) and nthlargest(2, volume) to identify the two highest volumes and calculated the vol * Price. But I am not able to insert a summary to sum the two values (and also not able to do a running total of the vol*price field.
Any help on how I can achieve this is very highly appreciated.
Thanks and regards,
TR