Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kmstephenson
Creator
Creator

MIN/MAX for sparkline

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!

17 Replies
sunny_talwar

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

kmstephenson
Creator
Creator
Author

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?

SparklineTest.PNG

sunny_talwar

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

kmstephenson
Creator
Creator
Author

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

sunny_talwar

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

kmstephenson
Creator
Creator
Author

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

SparklineTest.PNG

sunny_talwar

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

Best,

Sunny

kmstephenson
Creator
Creator
Author

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!

kmstephenson
Creator
Creator
Author

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!