25 Replies Latest reply: Jun 23, 2017 11:32 AM by Linda Pembroke

# Set Analysis not pulling desired field

Hi,

I am currently using the following expression to pull a 'Rate' for an entire year regardless of selections and it is working: Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) . What I am wanting to do now is assign a 'Star' value based on where that rate falls on the threshold table (between PtileBeg and PtileEnd). However, I need the formula to point to the max Year and Month's thresholds as they vary each month.

This is what I am trying to use but it isn't working:

if(Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS)>= only({<MaxYear,MaxMonth>}PtileBeg) and
Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS)<= only({<MaxYear,MaxMonth>}PtileEnd),only({<MaxYear,MaxMonth>}Star))

Any help is much appreciated.

• ###### Re: Set Analysis not pulling desired field

May be this

If(Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) >=

Only({<Year = {<\$(=Max(Year))>}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} PtileBeg)

and
Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) <=

Only({<Year = {<\$(=Max(Year))>}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} PtileEnd),

Only({<Year = {<\$(=Max(Year))>}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} Star))

• ###### Re: Set Analysis not pulling desired field

Thank you, but I am getting errors and am not sure what may be wrong.

• ###### Re: Set Analysis not pulling desired field

If(Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) >=

Only({<Year = {\$(=Max(Year))}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} PtileBeg)

and
Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) <=

Only({<Year = {\$(=Max(Year))}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} PtileEnd),

Only({<Year = {\$(=Max(Year))}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} Star))

• ###### Re: Set Analysis not pulling desired field

I was hoping this would work but it didn't. I think the issue is because of trying to point to the max month/year Ptile. When I just try to show that value it isn't working either. For example, I'm putting this in a text box just to see the value and am getting an error: Only({<Year = {\$(=Max(Year))}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} PtileBeg)

• ###### Re: Set Analysis not pulling desired field

What error message do you see?

• ###### Re: Set Analysis not pulling desired field

Actually it's just null '-', not an error.

• ###### Re: Set Analysis not pulling desired field

Try with Max

Max({<Year = {\$(=Max(Year))}, Month = {"\$(=Max({<Year = {\$(=Max(Year))}>}Month))"}>} PtileBeg)

• ###### Re: Set Analysis not pulling desired field

Same thing I also tried this and not getting any result: =max({<Month = {\$(=Max(Month))}>} PtileBeg)

• ###### Re: Set Analysis not pulling desired field

How do you create Month in your script?.

• ###### Re: Set Analysis not pulling desired field

Month(ReportDate) as Month

• ###### Re: Set Analysis not pulling desired field

Try creating another field like this

Month(ReportDate) as Month,

Num(Month(ReportDate)) as MonthNum

and now try this

Max({<Year = {\$(=Max(Year))}, MonthNum = {"\$(=Max({<Year = {\$(=Max(Year))}>}MonthNum))"}>} PtileBeg)

• ###### Re: Set Analysis not pulling desired field

Thank you, I was just trying that. It pulls the value now, but it is pulling based on the month selected. I am needing it to show the max month value regardless of month selection. Is that possible?

• ###### Re: Set Analysis not pulling desired field

Try this

Max({<Year = {\$(=Max({1} Year))}, MonthNum = {"\$(=Max({1<Year = {\$(=Max({1}Year))}>}MonthNum))"}>} PtileBeg)

• ###### Re: Set Analysis not pulling desired field

You are awesome, thank you so much! I think I am in need of a set analysis class

• ###### Re: Set Analysis not pulling desired field

Hi Sunny,

This is working for what I originally asked, thank you. However, now I am needing to use this in a pivot table with Year as dimension. How can I modify this to show the max MonthNum PtileBeg value by each Year regardless of Year selection? I have tried several options and cannot get a value to show for both years, only one at a time based on selection.

Thank you again!

• ###### Re: Set Analysis not pulling desired field

You will have to elaborate a little bit more for me... can you share a snapshot of what you are seeing and explain what you would want to see?

• ###### Re: Set Analysis not pulling desired field

Absolutely.

The first part of the pivot shows the totals based on selection (ID, Name, etc) and the CP part shows grand totals regardless of selection. I am having trouble with the star values now. I need the formula to point to the max month's Ptile (rate) thresholds by Year to pull the correct Star value. Here is what I'm using now but I know something isn't right

=if(Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)>= Max({<Year= , MonthNum = {"\$(=Max({<Year = >}MonthNum))"}>} STAR1) and
Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)< Max({<Year= , MonthNum = {"\$(=Max({<Year = >}MonthNum))"}>} STAR2),1, and so on...

• ###### Re: Set Analysis not pulling desired field

What is Star value, I am still having difficulty in understanding your requirement.... Do you think you can share a sample to look at?

• ###### Re: Set Analysis not pulling desired field

Hi Sunny,

I have a table that shows star values based on a rate threshold. You can see it in my sample. Based on the max month's thresholds for each Year I need to show the overall star value (CP Star) regardless of month/year selection. I really appreciate all of your help!

Thank you,

Linda

• ###### Re: Set Analysis not pulling desired field

What are the number you are hoping to see here for these selections

• ###### Re: Set Analysis not pulling desired field

Yes, that pivot table is the one I am talking about with the 'CP Star' expression. For any selection I want to see a fixed 5 star for 2016 because based on the CP Rate of 86.37%, that falls into the 5 star threshold category for December 2016 (max month). For 2017 I want to see a 3 star because the CP Rate 87.85% falls into the 3 star threshold category for May 2017 (current max month). Sorry, I know it's confusing

• ###### Re: Set Analysis not pulling desired field

Try this

=if(Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)>= Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR1)), Year, MonthNum)) and

Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)< Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR2)), Year, MonthNum)),1,

if(Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)>= Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR2)), Year, MonthNum)) and

Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)< Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR3)), Year, MonthNum)),2,

if(Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)>= Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR3)), Year, MonthNum)) and

Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)< Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR4)), Year, MonthNum)),3,

if(Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)>= Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR4)), Year, MonthNum)) and

Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)< Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR5)), Year, MonthNum)),4,

if(Sum({1}NUM_STATUS)/Sum({1}DEN_STATUS)>= Max({<Year, Month>} Aggr(If(Only({1}MonthNum) = Max(TOTAL <Year> {<Year, Month>}MonthNum), Only({1} STAR5)), Year, MonthNum)),5)))))

• ###### Re: Set Analysis not pulling desired field

So far, so good! I have never seen the TOTAL function used before so I am continuing to learn. I can't thank you enough!