topic Re: Set Analysis not pulling desired field in QlikView

Set Analysis not pulling desired field

byrnel0586 — Wed, 14 Jun 2017 16:13:46 GMT

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

sunny_talwar — Wed, 14 Jun 2017 16:26:03 GMT

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

byrnel0586 — Wed, 14 Jun 2017 16:39:51 GMT

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

Re: Set Analysis not pulling desired field

Anonymous — Wed, 14 Jun 2017 16:41:14 GMT

I don't know if this is helpful but it might help you get rid of some of the complexity in your expression.

vlookup function in QlikView | Qlik Community

Re: Set Analysis not pulling desired field

sunny_talwar — Wed, 14 Jun 2017 16:56:15 GMT

My bad, try 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

byrnel0586 — Fri, 16 Jun 2017 15:11:29 GMT

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

sunny_talwar — Fri, 16 Jun 2017 15:13:36 GMT

What error message do you see?

Re: Set Analysis not pulling desired field

byrnel0586 — Fri, 16 Jun 2017 15:16:00 GMT

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

Re: Set Analysis not pulling desired field

sunny_talwar — Fri, 16 Jun 2017 15:21:01 GMT

Try with Max

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

Re: Set Analysis not pulling desired field

byrnel0586 — Fri, 16 Jun 2017 15:24:25 GMT

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

Re: Set Analysis not pulling desired field

sunny_talwar — Fri, 16 Jun 2017 15:29:47 GMT

How do you create Month in your script?.

Re: Set Analysis not pulling desired field

byrnel0586 — Fri, 16 Jun 2017 15:35:07 GMT

Month(ReportDate) as Month

Re: Set Analysis not pulling desired field

sunny_talwar — Fri, 16 Jun 2017 15:42:55 GMT

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

byrnel0586 — Fri, 16 Jun 2017 15:51:12 GMT

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

sunny_talwar — Fri, 16 Jun 2017 15:52:50 GMT

Try this

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

Re: Set Analysis not pulling desired field

byrnel0586 — Fri, 16 Jun 2017 15:55:07 GMT

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

sunny_talwar — Fri, 16 Jun 2017 15:56:27 GMT

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

Re: Set Analysis not pulling desired field

byrnel0586 — Wed, 21 Jun 2017 20:37:58 GMT

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

sunny_talwar — Thu, 22 Jun 2017 11:10:17 GMT

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

byrnel0586 — Thu, 22 Jun 2017 12:40:09 GMT

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