MIN/MAX for sparkline

[kmstephenson]

Hi All,

I would like to add expressions on my straight table to measure the minimum and maximum value displayed in the sparkline mini chart on the table. This is the expression used for sparkline:

COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL {<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd)

Dimension used: QuarterEndDt

EndDt is set to Always One Selected Value in this application which is why it is ignored in Set Analysis.

What code should I use to calculated a minimum and maximum value for this sparkline?

Thanks!

[sunny_talwar]

It seems that you are now talking about the sparklines themselves? or is it still related to the two new expressions? What is the expression you are using to determine the highlighting?

[kmstephenson]

Yes, question (1) is the sparkline itself. In Mini Chart Settings I have Highlight Max Value With Color and Highlight Min Value With Color both selected. What does QlikView do if there are two maximums or minimums? Highlight both? Or choose first or last?

Once I know this, I want to leverage the code above to create 2 additional columns on the table. In addition to Minimum % and Maximum %, I want to create Minimum Quarterly Rolling Year and Maximum Quarterly Rolling Year to display QuarterEndDt associated with those minimum and maximum values.

[sunny_talwar]

Ideally, it should highlight both, but I am not sure what expression you are using to give you the right answer.

[kmstephenson]

Thanks, Sunny! Also, how do I report the QuarterEndDt associated with these maximums and minimums:

Max({<EndDt,QuarterEndDt-={''}>}Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL {<EndDt,QuarterEndDt-={''}>}DISTINCT {<Event>}KeyPatEnd), Event,QuarterEndDt))

Min({<EndDt,QuarterEndDt-={''}>}Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL {<EndDt,QuarterEndDt-={''}>}DISTINCT {<Event>}KeyPatEnd), Event,QuarterEndDt))

[sunny_talwar]

Report the QuarterEndDt? I am not sure I follow.... I don't even know what are we exactly solving for... are we still on one issue or are these stems from one issue going into another one and then another one. You have me all confused now

[kmstephenson]

Sorry! It's another issue. With your help, I was able to find that the answer to my question originally posted was shown below. These accurately display the correct Minimum % and Maximum % of the highlighted values on the sparkline.

Max({<EndDt,QuarterEndDt-={''}>}Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL {<EndDt,QuarterEndDt-={''}>}DISTINCT {<Event>}KeyPatEnd), Event,QuarterEndDt))

Min({<EndDt,QuarterEndDt-={''}>}Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL {<EndDt,QuarterEndDt-={''}>}DISTINCT {<Event>}KeyPatEnd), Event,QuarterEndDt))

In addition to this, I was hoping to have 2 additional columns that display the QuarterEndDt of the Minimum  % and Maximum % (i.e. which QuarterEndDt is highlighted as the Minimum and Maximum on the sparkline). This is related to the first question, only instead of showing the % of the minimum and maximum, I want to see the QuarterEndDt of the minimum and maximum. Does this make sense?

[sunny_talwar]

May be this

FirstSortedValue({<EndDt,QuarterEndDt-={''}>} QuarterEndDt, -Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL {<EndDt,QuarterEndDt-={''}>}DISTINCT {<Event>} KeyPatEnd), Event,QuarterEndDt))

FirstSortedValue({<EndDt,QuarterEndDt-={''}>} QuarterEndDt, Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL {<EndDt,QuarterEndDt-={''}>}DISTINCT {<Event>} KeyPatEnd), Event,QuarterEndDt))

Do you know what the red highlighted part is used for?

[kmstephenson]

Just tested this and this is perfect! Thank you!!