Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
What is Star value, I am still having difficulty in understanding your requirement.... Do you think you can share a sample to look at?
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
I am guessing we are talking about this column?
What are the number you are hoping to see here for these selections
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
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)))))
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!