16 Replies Latest reply: Dec 8, 2017 2:05 PM by Sunny Talwar RSS

    MIN/MAX for sparkline

    Kristina Stephenson

      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

          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
              Kristina Stephenson

              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

                • Re: MIN/MAX for sparkline
                  Sunny Talwar

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

                    • Re: MIN/MAX for sparkline
                      Kristina Stephenson

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

                        • Re: MIN/MAX for sparkline
                          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))

                            • Re: MIN/MAX for sparkline
                              Kristina Stephenson

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

                               

                              SparklineTest.PNG

                                • Re: MIN/MAX for sparkline
                                  Sunny Talwar

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

                                   

                                  Best,

                                  Sunny

                                    • Re: MIN/MAX for sparkline
                                      Kristina Stephenson

                                      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
                                        Kristina Stephenson

                                        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

                                            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
                                                Kristina Stephenson

                                                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

                                                    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
                                                        Kristina Stephenson

                                                        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

                                                            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
                                                                Kristina Stephenson

                                                                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

                                                                    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?