Set analysis to get max Month per year in a chart.

[#1]

How can I do this in set analysis? I want to get the latest month per year.

Year	Max(Month)	Amount	Comment
2015	12	$100.00	Amount for Dec 2015
2016	7	$200.00	Amount for July 2015
2017	-	$0.00

If I use this expression the max month is always 12.

SUM({< Month= {"$(=max({Month))"}>}  Amount)

Result.

Year	Max(Month)	Amount
2015	12	$100.00
2016	7	0
2017	-	0

expected result should be this.

Year	Max(Month)	Amount
2015	12	$100.00
2016	7	$200.00
2017	-	$0.00

[Chanty4u]

try

Firstsortedvalue(Month,-Amount)

or

Firstsortedvalue(Amount,-Month)

[tresesco]

Set analysis would not work here since it doesn't evaluate row-wise but once for a chart.

Try like:

FirstSortedValue(Aggr(Sum(Amount),Month), -Month)

[#4]

I tried getting the sum. but it's returning null. Not sure why it does not work in my data, 

[#5]

i have multiple rows Amount with month = Max month.  Looks like this only works with one row as max Month. 

[NZFei]

Check this out.

Dimension: Year, Month

Expression: if(Month=max(total <Year> Month),1,0)*sum(Sales)

Fei

[#7]

Dude you're the best! high 5!

[#8]

I tried now showing the Month field but it does not work. do you have other solution for this?

[tresesco]

If you have multiple rows of Amount against max month, what do you want to do with them? Should they be summed up? If yes, my proposed solution above works that way. If the Amount values are just repeat (same) you can simply use Distinct like:

FirstSortedValue(Distinct Amount, -Month)

If possible, post a sample qvw.

[NZFei]

What is the problem now?

I have added some more lines and it is still working for me.