4 Replies Latest reply: Sep 6, 2017 11:15 AM by Ford Torrey RSS

    Accumulating Counts in Line Chart

    Ford Torrey

      Hello Qlik Community,

       

      New to Qlik Sense here (first post) and would greatly appreciate some help.

       

      I'm trying to create a line graph which accumulates the count of event registrations based on the number of days since registration opened and is responsive to user filtering. Below is my current data model. 'EventsInfo' is a table of the events with details on the event, and 'EventRegsSort' is a table of individual customer registrations corresponding to each event.

       

      dataModel.PNG

      So far I have created the following graph depicting the numbers of registrations on a certain day since registration opened by simply adding 'DaysOpen' and EventYear' as dimensions and counting 'CustomerID' as the measure. The graph below as been filtered down to two years of data for one event using a filter pane.

      Count(distinct CustomerID)
      

      graph1.PNG

      What I'm now trying to do is calculate and display the total number of registrations per day after registration opening, which would be calculated by the previous day's registration count plus the current day's registration count. I tried to do this using the following expression which resulted in the graph below.

      RangeSum(Above(Count(distinct(CustomerID)),0,RowNo()))
      

      graph2.PNG

      It looks like what's happening is the number of registrations on day X for 2016 is being added to the number of registrations on day X for 2017, and the line for 2017 is then displaying that value for day X, summarized below.

      YearDayNumber of Registrations
      201601120
      201701080
      201702200
      20161710
      20171484
      201711194

      Whereas the result I'm trying to get would be the figures above for day 0 for 2016 and 2017, and then for day 1 would be those number plus the numbers for day 1 as such:

      YearDayNumber of Reigstrations
      201601120
      201701080
      201611120 + 710 = 1830
      201711080 + 484 = 1564

      and so on for the remainder of the days.

       

      I've also tried the following thinking that the registration totals for each day needed to be calculated and sorted, but the result was the same as the graph shown above:

      RangeSum(Above(Aggr(Count(distinct CustomerID), EventYear, DaysOpen, (EventYear, (DaysOpen, (NUMERIC, ASCENDING)))), 0, RowNo()))
      

       

      Also, I was able to get this to work in a straight table using the first expression above (shown below), but can't seem to get it to work with the line chart which makes me think it may be some kind of sorting issue, but I'm not sure how to address it (or if this is even the case).

      RangeSum(Above(Count(distinct(CustomerID)),0,RowNo()))

      table1.PNG

       

      Again, any help would be greatly appreciated. Sorry for the long winded question if this is an easy fix, but it's had me stumped all day and none of the various accumulation related questions/answers I've found have worked.

       

      Thanks,

       

      Ford

        • Re: Accumulating Counts in Line Chart
          Sunny Talwar

          Try this

           

          Aggr(RangeSum(Above(Count(DISTINCT CustomerID), 0, RowNo())) , Year, (DaysOpen, (Numeric)))

            • Re: Accumulating Counts in Line Chart
              Ford Torrey

              Ah I was so close! Thanks so much Sunny this works perfectly!

               

              I'll note for any future readers that 'Year' in your expression needs to be changed to 'EventYear' in my case since just including 'Year' will call the 'Year()' function.

               

              For my personal information, could you provide a brief explanation to why this works as opposed to the expression I tried above that contained 'Aggr()'?

                • Re: Accumulating Counts in Line Chart
                  Sunny Talwar

                  There were couple of issues

                   

                  RangeSum(Above(Aggr(Count(distinct CustomerID), EventYear, DaysOpen, (EventYear, (DaysOpen, (NUMERIC, ASCENDING)))), 0, RowNo()))

                   

                  You are aggregating over EventYear and DaysOpen twice... you don't need that... and honestly since your first DaysOpen isn't sorted in ascending order it will be sorted in the sort order, which is not what you want... So we come to this:

                   

                  RangeSum(Above(Aggr(Count(distinct CustomerID), EventYear, (DaysOpen, (NUMERIC, ASCENDING))), 0, RowNo()))

                   

                  Now, the only reason to use Aggr() was to make sure that accumulation is done in a certain order... but since the RangeSum(Above()) is outside of Aggr() it is not even benefiting from the Aggr's sorting. In order to benefit from it, RangeSum(Above()) needs to be inside Aggr()

                   

                  Aggr(RangeSum(Above(Count(DISTINCT CustomerID), 0, RowNo())) , Year, (DaysOpen, (Numeric)))

                   

                  Does this make sense?