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.

[vishsaggi]

Try this may be:

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

[byrnel0586]

Thank you, but that is returning zero. My dimension is PHARM_Year if that is useful.

[vishsaggi]

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)

[byrnel0586]

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.

[sunny_talwar]

Your chart dimension is Year to Date... is this different from Year field?

[byrnel0586]

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?

[sunny_talwar]

Can you post what I added again... I can tell you what changes you can make with it

[byrnel0586]

Sure.

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

[sunny_talwar]

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)