topic Re: MIN/MAX for sparkline in QlikView

MIN/MAX for sparkline

kmstephenson — Mon, 04 Dec 2017 18:31:08 GMT

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!

Re: MIN/MAX for sparkline

sunny_talwar — Mon, 04 Dec 2017 19:30:08 GMT

May be this

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

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

Re: MIN/MAX for sparkline

kmstephenson — Mon, 04 Dec 2017 20:12:10 GMT

Thanks for the response, Sunny! It doesn't appear to calculate correctly.

Firstly, I want the values of both the minimum and maximum % for each row (value in dimension Events). Also, the values that are displayed for one row using the code above are not calculated correctly. The maximum is closer to 4% and the minimum lower.

Any ideas?

Re: MIN/MAX for sparkline

sunny_talwar — Mon, 04 Dec 2017 20:14:42 GMT

What is the chart dimension? What is the dimension for the sparkline?

Re: MIN/MAX for sparkline

kmstephenson — Mon, 04 Dec 2017 20:17:13 GMT

The chart dimension is Event. The dimension for the sparkline is QuarterEndDt.

Re: MIN/MAX for sparkline

sunny_talwar — Mon, 04 Dec 2017 20:19:17 GMT

May be try this out

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

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

Re: MIN/MAX for sparkline

kmstephenson — Mon, 04 Dec 2017 20:22:58 GMT

That now populates minimums and maximums for every row, but the values are all too low.

Re: MIN/MAX for sparkline

sunny_talwar — Mon, 04 Dec 2017 20:24:18 GMT

Would you be able to share a sample to look at?

Best,

Sunny

Re: MIN/MAX for sparkline

kmstephenson — Mon, 04 Dec 2017 20:54:49 GMT

I was able to determine that the denominator was being incorrectly calculated and it looks like all that was missing was this:

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

Thanks!

Re: MIN/MAX for sparkline

kmstephenson — Tue, 05 Dec 2017 14:14:20 GMT

Hi Sunny - another couple related questions you might be able to help with as well.

(1) What does QlikView do if more than one value is equal to the minimum or maximum? Will it highlight the most recent one on the sparkline? Or the first one? Or Both?

(2) The above code works for finding the Minimum % and Maximum %. What if I want to return the date value (QuarterEndDt) for the minimum and maximum %? This question partly relates to question (1) because I would need to know which date would be returned if there are more than one minimum or maximum.

Thanks!

Re: MIN/MAX for sparkline

sunny_talwar — Tue, 05 Dec 2017 14:38:48 GMT

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?

Re: MIN/MAX for sparkline

kmstephenson — Tue, 05 Dec 2017 19:31:04 GMT

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.

Re: MIN/MAX for sparkline

sunny_talwar — Wed, 06 Dec 2017 00:00:36 GMT

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

Re: MIN/MAX for sparkline

kmstephenson — Thu, 07 Dec 2017 14:14:51 GMT

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

Re: MIN/MAX for sparkline

sunny_talwar — Thu, 07 Dec 2017 14:23:33 GMT

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

Re: MIN/MAX for sparkline

kmstephenson — Thu, 07 Dec 2017 15:45:24 GMT

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?

Re: MIN/MAX for sparkline

sunny_talwar — Fri, 08 Dec 2017 19:05:38 GMT

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?

Re: MIN/MAX for sparkline

kmstephenson — Tue, 19 Dec 2017 17:26:05 GMT

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