Set Analysis not pulling desired field

[byrnel0586]

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.

[sunny_talwar]

How do you create Month in your script?.

[byrnel0586]

Month(ReportDate) as Month

[sunny_talwar]

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)

[byrnel0586]

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?

[sunny_talwar · Accepted solution]

Try this

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

[byrnel0586]

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

[sunny_talwar]

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

[byrnel0586]

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!

[sunny_talwar]

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?

[byrnel0586]

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