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.
Try this
=Sum({<PHARM_MonthNum, PHARM_Year>}Aggr(If(Only({1}PHARM_MonthNum) = Max({1} TOTAL <PHARM_Year> Aggr(Only({1}PHARM_MonthNum), PHARM_MonthNum, PHARM_Year)), Sum({<PHARM_MonthNum, PHARM_Year>} PHARM_NUM_STATUS)), PHARM_MonthNum, PHARM_Year))
Try this may be:
= Sum({< PHARM_MonthNum = {"$(= Max(PHARM_MonthNum)) "} >}PHARM_NUM_STATUS)
Thank you, but that is returning zero. My dimension is PHARM_Year if that is useful.
So what is this PHARM_MonthNum? May be use this if not can you share a sample to look into?
= Sum({< PHARM_Year, PHARM_MonthNum = {"$(= Max(PHARM_MonthNum)) "} >}PHARM_NUM_STATUS)
That still shows as 0.
PHARM_MonthNum is the month that I want to see max value for. I do not have a sample app, but I have attached a screenshot of an example. Month 7 is max month, and the NUM = 123. That is the value I would like to see in the pivot chart below where the Num = 0. I need the expression to always point at max month with any selection.
Your chart dimension is Year to Date... is this different from Year field?
No, I just renamed the Year label as Year to Date. I tried what you posted a few minutes ago and it works, but changes when I select another month. Is there a way to keep the values static to the max month only?
Can you post what I added again... I can tell you what changes you can make with it
Sure.
Sum({<PHARM_Year, PHARM_MonthNum = {"=PHARM_MonthNum = Max(TOTAL <PHARM_Year> Aggr(PHARM_MonthNum, PHARM_MonthNum, PHARM_Year))"}>} PHARM_NUM_STATUS)
Try this
Sum({<PHARM_Year, PHARM_MonthNum = {"=Only({1} PHARM_MonthNum) = Max({1} TOTAL <PHARM_Year> Aggr(Only({1} PHARM_MonthNum), PHARM_MonthNum, PHARM_Year))"}>} PHARM_NUM_STATUS)