Selecting only latest status in the month for aggregation

[#1]

Hi - My dataset has Amount by Month and Stage. I would like to display the total amount by month for the latest Stage in that month. For e.g. my data set is,

'Jan' 'Stage A' '10.00'

'Feb' 'Stage A' '10.00'

'Feb' 'Stage B' '10.00'

'Mar' 'Stage B' '10.00'

Assuming that Stage B is higher than Stage A. For the bar chart I have Stage as the dimension and Sum(Amount) as the expression. When I select Feb as a month, the chart shows $10 for Stage A and $10 for Stage B. I want to show only the latest stage in the selected month. So, for the month of Feb, the chart should show just $10 for Stage B. How can I implement this without changing the LOAD script? I am on QV 8.2. Thanks in advance. -Najma

[johnw]

I don't know that this is the simplest expression, and I can't verify that it works in version 8.2, but it doesn't require set analysis, doesn't require load script changes, and appears to work in version 8.5.

sum(aggr(if(Stage=maxstring(total <Month> Stage),Amount),Month,Stage))

[tabletuner]

I assume amount is constant in case of multiple stages per month. Then you can use the following expression:

sum

(DISTINCT Amount)

Please let me know if this was helpful.

[#4]

This is great! It seems to be working - had to modify it a bit for my purpose. Thank you very much for the solution. -Najma

[#5]

I had tried sum(distinct Amount), it didn't work for my requirements. Thank you. -Najma