Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
byrnel0586
Creator
Creator

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.

25 Replies
sunny_talwar

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

byrnel0586
Creator
Creator
Author

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

sunny_talwar

I am guessing we are talking about this column?

Capture.PNG

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

Capture.PNG

byrnel0586
Creator
Creator
Author

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

sunny_talwar

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

Capture.PNG

byrnel0586
Creator
Creator
Author

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!