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.
How do you create Month in your script?.
Month(ReportDate) as Month
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)
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?
Try this
Max({<Year = {$(=Max({1} Year))}, MonthNum = {"$(=Max({1<Year = {$(=Max({1}Year))}>}MonthNum))"}>} PtileBeg)
You are awesome, thank you so much! I think I am in need of a set analysis class
Hahahaha yes that would be good. Anyways, if you got everything you were looking for, I would request you to close this thread by marking correct and helpful responses.
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny
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!
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?
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...