4 Replies Latest reply: Dec 23, 2016 5:56 AM by Shubham Singh RSS

    Sum of Count of top 15 items

    Cheryl Gonsalves

      I have an expression that calculates the count of certain complaints. I have then calculated the sum of those counts based on te product component. But the thing I need now is the sum of top 15 counts.

      I want this to be done in the expression itself since I want to display this  separately in a text object. Below is the expression I have come up with so far. Seeking further help. Thanks in advance.

       

      =Sum(aggr(Count({<CAL_QUARTER={$(vPrevQuarter)},DealerZone={'WEST'}>}[Complaint Number]), [Product Component]))

        • Re: Sum of Count of top 15 items
          Gysbert Wassenaar

          Perhaps like this:


          =Sum(aggr(Count({<[Product Component]={"=rank(Count({<CAL_QUARTER={$(vPrevQuarter)},DealerZone={'WEST'}>}[Complaint Number]))<=15"},CAL_QUARTER={$(vPrevQuarter)},DealerZone={'WEST'}>}[Complaint Number]), [Product Component]))

          • Re: Sum of Count of top 15 items
            Shubham Singh

            Here I did this in Consumer Sales app that is provided with Qlik Sense as a demo.

             

            I used the bar chart Sales Rep Revenue in Sales Rep Performance Sheet.

             

            Create a variable like this.

            Capture.JPG

            * I am doing this for Sum of Top 5 that's why you'll find a 6, Don't forget equal sign.

             

            Then use below expression.

             

            Sum(

              aggr(

              if( Sum( [Sales Quantity]*[Sales Price])>$(vSum)+0.001,

                    Sum( [Sales Quantity]*[Sales Price]),

                        0

                      ),

              [Sales Rep Name]

              )

            )

             

            For some unknown reason I have to add a minute value in $(vSum).

             

            You will get Sum for top 5 Sales Rep.

             

            Hope you can replicate this in your app.

              • Re: Sum of Count of top 15 items
                Cheryl Gonsalves

                Thank for the suggestion.

                I think the below example would explain my exact requirement. As shown in the below bar graph, it has the count of complaints for a region based on certain criteria. I have set the dimension limit(for Product component) to top 15. Now I need to find the total of the values on the data points(i.e 221) and display in in the title.

                bar_Graph.PNG

                 

                the expression that used for the count is

                 

                =Count({<CAL_YEAR={'$(=Max(CAL_YEAR))'},CAL_QUARTER={$(vPrevQuarter)},DealerZone={'WEST'}>}[Complaint Number])

                  • Re: Sum of Count of top 15 items
                    Shubham Singh

                    In my answer I calculated 6th largest value (vSum) and then in sum expression, I added only values that were greater than that 6th value.

                     

                    So your vSum will be

                    =max(

                        aggr(

                                  Count({<CAL_YEAR={'$(=Max(CAL_YEAR))'},CAL_QUARTER={$(vPrevQuarter)},DealerZone={'WEST'}>}[Complaint Number]),

                                  [Product Component]

                              ),

                                  16

                        )


                    And your Sum expression will be

                     

                    Sum(

                      aggr(

                      if( Count({<CAL_YEAR={'$(=Max(CAL_YEAR))'},CAL_QUARTER={$(vPrevQuarter)},DealerZone={'WEST'}>}[Complaint Number])           >$(vSum)+0.001,

                            Count({<CAL_YEAR={'$(=Max(CAL_YEAR))'},CAL_QUARTER={$(vPrevQuarter)},DealerZone={'WEST'}>}[Complaint Number]),

                                0

                              ),

                      [Product Component]

                      )

                    )