Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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?
What is the chart dimension? What is the dimension for the sparkline?
The chart dimension is Event. The dimension for the sparkline is QuarterEndDt.
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))
That now populates minimums and maximums for every row, but the values are all too low.
Would you be able to share a sample to look at?
Best,
Sunny
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!
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!