Set Analysis - sum max month value

[byrnel0586]

Hi all,

I am needing to sum a value (PHARM_NUM_STATUS) for only the max month of the year. I am currently using the below expression, but it is summing the values for all months of the year.

Sum({<PHARM_Year,PHARM_MonthNum>}PHARM_NUM_STATUS)

How can I modify this to only sum the max(PHARM_MonthNum) PHARM_NUM_STATUS?

Thank you in advance.

[byrnel0586]

Thanks. For some reason it is working for 2016 but showing 0 for 2017...

[sunny_talwar]

Without the changes it was working for both 2017 and 2016?

[byrnel0586]

Yes, it was working but changing with month selections. I think somewhere in the new expression it's pointing to the max month of all years rather than pivoting by each year? Makes sense to work for 2016 that way since the max month is 12 and max month for 2017 is only 7.

[sunny_talwar]

Try this

Sum({<PHARM_Year, PHARM_MonthNum = {"=Only({1}PHARM_MonthNum) = Max({1<Year = {$(=Max({1} Year))}>} TOTAL <PHARM_Year> Aggr(Only({1}PHARM_MonthNum), PHARM_MonthNum, PHARM_Year))"}>} PHARM_NUM_STATUS)

[sunny_talwar]

I wonder why this wouldn't work?

=Sum({<PHARM_Year, PHARM_MonthNum = {"$(=Max({1<PHARM_Year = {"$(=Max({1}PHARM_Year))"}>} PHARM_MonthNum))"}>} PHARM_NUM_STATUS)

[byrnel0586]

This one still shows 0 for 2017.

[byrnel0586]

This one is giving an error

[sunny_talwar]

My bad, remove the inner double quotes

=Sum({<PHARM_Year, PHARM_MonthNum = {"$(=Max({1<PHARM_Year = {$(=Max({1}PHARM_Year))}>} PHARM_MonthNum))"}>} PHARM_NUM_STATUS)

[byrnel0586]

Thank you. This one is now pulling the sum for month 7 for both 2016 and 2017

[byrnel0586]

Any other ideas? If you can't figure it out it must not be possible.