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]

May be its the time to share you sample app for a quick look into it.. You can scramble data using below article:

Preparing examples for Upload - Reduction and Data Scrambling

[byrnel0586]

Ok sure, I hope this helps. Thank you both.

It is the 'Num' expression in the pivot chart under the bar chart that we are trying to work on.

[sunny_talwar · Accepted solution]

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))

[byrnel0586]

Thank you, Sunny! As always, you are a life saver!

[panosalexand]

Worked for me 👌