Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Thanks. For some reason it is working for 2016 but showing 0 for 2017...
Without the changes it was working for both 2017 and 2016?
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.
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)
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)
This one still shows 0 for 2017.
This one is giving an error
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)
Thank you. This one is now pulling the sum for month 7 for both 2016 and 2017
Any other ideas? If you can't figure it out it must not be possible.