22 Replies Latest reply: Nov 29, 2017 9:17 PM by Hung dep RSS

    How to distinct count cumulative with date ranges

    Hung dep

      Hi experts,

      I got data like this table:

       

      Outlet IDDateSalesOrder status
      11111/1/201710Approved
      11211/1/201720Rejected
      11311/1/201730Rejected
      11111/1/201790Rejected
      11811/1/2017120Approved
      11111/4/201740Approved
      11311/4/201760Approved
      11511/4/2017100Approved
      11711/4/2017110Rejected
      11911/4/2017130Rejected
      11211/5/201750Rejected
      11411/5/201770Approved
      11511/6/201780Approved
      11211/7/201750Approved
      11411/7/201770Rejected

       

      I want to count cumulative outlets by date like this:

       

      DateCount distinct Outlet ID with total sales >=45, order status ="approved"Count cumulative Outlet with sales >=45 and order status ="approved"
      11/1/201711
      11/4/201724
      11/5/201715
      11/6/201715
      11/7/201716
      Grand Total66

       

       

       

       

      Can anyone help me the expression for the two ways of counting outlet ID, especially the second count which means Total cumulative outlets with sales >=45 and sales order status must be "approved" only.

       

      Thank you a lot from Vietnam.

        • Re: How to count cumulative with date ranges
          Mohammed Mukram

          Hi,

           

          Can you try :

           

          =rangesum(Above(Count({<[Order status]={'Approved'},Sales={">45"}>}[Outlet ID]),0, RowNo()))


          PFA.

            • Re: How to count cumulative with date ranges
              Hung dep

              Thanks Mohammed but it's not really correct.

              Your solution does not Sum(Sales) by outlet ID. Eg: outlet ID 111, day 11/1: sales = 10 and day 11/4: sales = 40 --> Total sales for outlet 111 should be 50 (status approved). Then by date 11/4 we should have 4 outlets with sales >= 45, your expression only count 3.

               

              One more thing with Rangesum is that it will sum all value by dimension:

              Eg:

              Day, outlet, sales

              day1, A, 100

              day1, B, 200

              day 2, A, 50

               

              -> result (Cumulative count):

               

              day 1: 2 outlets

              day 2: 1 outlet + 2 of the previous day = 3 outlets. However, this should be 2 outlets only as outlet A is repeated, not new one.

               

              Could you please reconsider it?

            • Re: How to count cumulative with date ranges
              Hung dep

              Can anyone help me...

                • Re: How to distinct count cumulative with date ranges
                  Amarnath Krishnasamy

                  Please modify your script in load as:

                   

                  NoConcatenate
                  test2:
                  LOAD
                  Date,
                  ID,
                  Status,
                  only(Sales) as Sales,
                  if(ID=peek('ID'),if(peek('CumSales')<45,peek('CumSales')+only(Sales),0),only(Sales)) as CumSales
                  RESIDENT tesT
                  where Status='Approved'
                  group by Date,ID,Status
                  order by ID, Date;

                  Concatenate(test2)
                  LOAD
                  Date,
                  ID,
                  Status,
                  only(Sales) as Sales,
                  only(Sales) as CumSales
                  RESIDENT tesT
                  where Status='Rejected'
                  group by Date,ID,Status
                  order by ID, Date;

                  NoConcatenate
                  test3:
                  LOAD *
                  RESIDENT test2
                  order by Date, ID;

                   

                   

                  drop tables test2;

                   

                  In Expression:

                   

                  Count1 = count({<Status={"Approved"}, Sales={">=45"}>}DISTINCT ID)

                  Count2= count({<Status={"Approved"}, CumSales={">=45"}>}DISTINCT ID)

                   

                  Hope this works for you!

                   

                    • Re: How to distinct count cumulative with date ranges
                      Hung dep

                      Hi bro,

                      I've tried to modified as your instruction, but, pardon, I am a newbie and several things I havent understood:

                      only(Sales) as Sales,
                      if(ID=peek('ID'),if(peek('CumSales')<45,peek('CumSales')+only(Sales),0),only(Sales)) as CumSales

                       

                      How do ONLY and PEEK work? And we havent got CumSales field, right? And cound you please help me with the full script for this app?

                      Bwt, I attached my data and app also sothat you may look at them. Thank you.

                        • Re: How to distinct count cumulative with date ranges
                          Amarnath Krishnasamy

                          Sorry, I dont have qlik sense - but this solution should work for your case.
                          // Load data from source file
                          tesT:
                          LOAD @1 as ID,
                          @2 as Date,
                          @3 as Sales,
                          @4 as Status
                          FROM
                          test.txt
                          (
                          txt, codepage is 1252, no labels, delimiter is spaces, msq, header is 1 lines);

                          /* Now, load those records which have status as approved and order it by ID and Date
                          We are also grouping them by Date, ID and Status to get aggregations work for CumSales

                          Here we are doing 2 things:

                          Sales Column - Just stores the actual sales data - we are using the only aggregation to rule out the error that would otherwise be thrown for not including this column in the group by fields.

                          CumSales Column - This field is not actually storing cumulative sum of sales but is just an identifier to see by what date a particular ID has reached the threshold of 45.
                          It uses peek to compare the current record value with the previously loaded record.

                          Here we are comparing if current ID is same as previous ID and also,
                          if the value under CumSales for that ID has not reached the threshold of 45 before loading this current record.
                          Only then it will sum up the current sales with previous value under CumSales column and add updated value to CumSales column. Otherwise, it will default to zero.

                          We are doing this to use CumSales as a field during our front end calculation for count2 so that we can filter - Sales>=45 and get the count

                          */


                          NoConcatenate
                          test2:
                          LOAD
                          Date,ID,Status,
                          only(Sales) as Sales,
                          if(ID=peek('ID'),if(peek('CumSales')<45,peek('CumSales')+only(Sales),0),only(Sales)) as CumSales
                          RESIDENT tesT
                          where Status='Approved'
                          group by Date,ID,Status
                          order by ID, Date;


                          // Just concatenating the records in status Rejected also - just to get all records into final table

                          Concatenate(test2)
                          LOAD
                          Date,ID,Status,
                          only(Sales) as Sales,
                          only(Sales) as CumSales
                          RESIDENT tesT
                          where Status='Rejected'
                          group by Date,ID,Status
                          order by ID, Date;

                          // Reloading the data for ordering by Date and ID - just in case if needed so.
                          NoConcatenate
                          test3:
                          LOAD *
                          RESIDENT test2
                          order by Date, ID;

                          drop tables tesT,test2;

                           

                          Please let me know if you still have issues in understanding any specific thing.

                          Or if this solution does not work for you

                            • Re: How to distinct count cumulative with date ranges
                              Hung dep

                              Hi bro,

                              Your explanation is quite good, I can catch up with that.

                              We seem to going on the right direction but It hasnt really solved my case.

                              Below is my result with your suggested count and two other expressions that I modified from yours and learnt from Sunny Talwar in this topic. His expressions seem to be better but also not really solve. Could you please revise that once again, we neally make it.

                               

                               

                              Datecount({<Status={"Approved"}, Sales={">=45"}>}DISTINCT [Outlet ID])count({<Status={"Approved"}, CumSales={">=45"}>} DISTINCT [Outlet ID])Count(
                              {$<[Outlet ID] = {"=Sum({$<[Order status]={'Approved'}>} Sales) >= 45"},[Order status]={'Approved'}>}
                              DISTINCT [Outlet ID])
                              Count(
                              {$<[Outlet ID] = {"=Sum({$<[Order status]={'Approved'}>} CumSales) >= 45"},[Order status]={'Approved'}>}
                              DISTINCT [Outlet ID])
                              11/1/20172222
                              11/4/20174433
                              11/5/20172211
                              11/6/20171111
                              11/7/20172211
                              • Re: How to distinct count cumulative with date ranges
                                Hung dep

                                Hi bro,

                                Although I got correct answer at the below section of this thread by Sunny Talwar, the solution seem to be not applicable for high volume data as it takes too long to reload and calculation timed out error on sheet.

                                 

                                I still hope your direction solve my problem and performance is good. Could you please keep an eye on this and work out the best way?

                                 

                                Thank you so much.

                        • Re: How to distinct count cumulative with date ranges
                          Sunny Talwar

                          May be using The As-Of Table approach

                           

                          Table:

                          LOAD * INLINE [

                              Outlet ID, Date, Sales, Order status

                              111, 11/1/2017, 10, Approved

                              112, 11/1/2017, 20, Rejected

                              113, 11/1/2017, 30, Rejected

                              111, 11/1/2017, 90, Rejected

                              118, 11/1/2017, 120, Approved

                              111, 11/4/2017, 40, Approved

                              113, 11/4/2017, 60, Approved

                              115, 11/4/2017, 100, Approved

                              117, 11/4/2017, 110, Rejected

                              119, 11/4/2017, 130, Rejected

                              112, 11/5/2017, 50, Rejected

                              114, 11/5/2017, 70, Approved

                              115, 11/6/2017, 80, Approved

                              112, 11/7/2017, 50, Approved

                              114, 11/7/2017, 70, Rejected

                          ];

                           

                          MinDate:

                          LOAD Min(Date) as MinDate

                          Resident Table;

                           

                          LET vMinDate = Peek('MinDate');

                          DROP Table MinDate;

                           

                          AsOfTable:

                          LOAD Date as AsOfDate,

                          Date(Date - IterNo() + 1) as Date

                          Resident Table

                          While Date - IterNo() + 1 >= $(vMinDate);

                           

                          Capture.PNG

                          • Re: How to distinct count cumulative with date ranges
                            Sunny Talwar

                            May be try this

                            Count(DISTINCT {<[Order status] = {'Approved'}>} Aggr(If(Sum({<[Order status] = {'Approved'}>} Sales) >= 45, [Outlet ID]), [Outlet ID], AsOfDate))

                             

                            Capture.PNG