6 Replies Latest reply: Nov 1, 2011 7:15 AM by Deepak Vadithala RSS

    Set analysis: Count ranges for sums

      Hello everyone,

       

      I am new in qlikview and I have an issue in a count a little bit complicated.

       

      I have tables with Sales by Product, Banner, Store and Month.

      I would like to count the number of store where my goad achievement (SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear)) are in ranges. So if I select 4 month and for my 200 stores for a few products:

      It can count that I have 80 stores under 105% , 60 between 105% and 110% and 60 above 110%. It also want to know that for my 200 store my average is about 105%.

       

      Is it possible with set analysis ? Or should I have to rethink my design ?

       

      Thanks for your help.

        • Set analysis: Count ranges for sums

          lookup IntervalMatch....that may help you get where you wish to.

            • Set analysis: Count ranges for sums

              I am not sure how I can use this function because It think it is meant to be used in my loading script.

              However, when I use my goal achivement it is dynamic:it changes is regarding the stores or the months or the product,... That I have selected. Moreover It cannot be calculated in my script because it is a division of sum.

               

               

              Any other ideas ?

               

               

              Thanks for your help.

                • Set analysis: Count ranges for sums
                  Stefan Wühl

                  try maybe something like

                   

                  count( aggr( if( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) < 0.05, Store), Store))

                   

                  resp.

                  count( aggr( if( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) >= 0.05

                  and SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) <= 0.1, Store), Store))

                   

                  resp.

                  count( aggr( if( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear) > 0.1, Store), Store))

                   

                  for the counts and

                   

                  avg( aggr( SUM(SalesCurrentYear-SalesLastYear) /SUM(SalesLastYear), Store))

                   

                  for the average.

                   

                  Since I don't know your data model, you might need to modify these expressions a little (or you maybe you could tell us a little more about your setting, best with uploading a sample).

                   

                  Also note that I used e.g. 10% instead of 110%, since your expression is calculating the surplus only (already subtracting 1 for the last years sales. Or use expressions like sum(SalesCurrentYear) / sum(SalesLastYear) .

                   

                  Or maybe I misunderstood that requirement, then just change the numbers to whatever you like.

                   

                  Regards,

                  Stefan