23 Replies Latest reply: Aug 24, 2017 10:34 AM by Linda Pembroke

# Set Analysis - sum max month value

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?

• ###### Re: Set Analysis - sum max month value

Try this may be:

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

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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)

• ###### Re: Set Analysis - sum max month value

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.

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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?

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

Sure.

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

• ###### Re: Set Analysis - sum max month value

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)

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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.

• ###### Re: Set Analysis - sum max month value

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)

• ###### Re: Set Analysis - sum max month value

This one still shows 0 for 2017.

• ###### Re: Set Analysis - sum max month value

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)

• ###### Re: Set Analysis - sum max month value

This one is giving an error

• ###### Re: Set Analysis - sum max month value

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)

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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.

• ###### Re: Set Analysis - sum max month value

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

• ###### Re: Set Analysis - sum max month value

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