topic Set Analysis Question in QlikView

Set Analysis Question

darrellbutler — Thu, 26 May 2011 10:33:29 GMT

Hi, I want to add some key points to my dashboard within a text box.The following set expression provides the highest price paid out by our competitors.

= 'Top Competitor Milk Price = '&

max({1<[Month] = {"$(=max(Month))"}, [Market] = {"Milk"}, [Company] -= {"First*"}>}PPL)

How would I also add who the competitor is ?My underlying model is simple it only includes Month, Company, Market, PPL.

Any help greatly appreciated.

Darrell.

Set Analysis Question

Thu, 26 May 2011 11:11:49 GMT

Hello Darrell,

You can try this: As you are using a Text box, Goto Properties-> Caption Check the box Show Caption and in the Title Text box type =COMPETITOR (this would be your actual competitor field name). By doing this, it will get you the competitor name as the heading for your Text object. Hope that helps.

ANDY

Re: Set Analysis Question

johnw — Fri, 27 May 2011 00:09:39 GMT

Probably something using the firstsortedvalue() function. Maybe something like this?

firstsortedvalue(Competitor,-max({1<[Month] = {"$(=max(Month))"}, [Market] = {"Milk"}, [Company] -= {"First*"}>}PPL))

I probably have a mistake there. But the intent is that it would, for each competitor, calculate the max value paid out by that specific competitor. Then it sorts those in reverse order (the minus sign), putting the maximum of those maximum values first. Then it takes the value of that competitor.

You might also have a look at the rank() function.