Set Analysis Question

[darrellbutler]

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.

[#2]

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

[johnw]

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.