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.
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.
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.
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.
|Year||Day||Number of Registrations|
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:
|Year||Day||Number of Reigstrations|
|2016||1||1120 + 710 = 1830|
|2017||1||1080 + 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).
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.