Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm tasked with the job on creating a cumulative sum over a dynamic time range.
Let's assume my customer has a list of users that are active on a daily basis. He now wants to know how many distinct users in total have been active - but through a dynamic time range:
Assuming these data
data:LOAD * INLINE [ Date, User 2018-01-01, A 2018-01-02, B 2018-01-05, A 2018-01-05, C 2018-02-03, A 2018-02-03, C 2018-02-08, D 2018-02-10, A 2018-02-12, C ](delimiter is ',');
Assuming if today is March 2018 and a YTD value is shown, the customer expects the following outcome:
2018-01-01 --> user A --> 1
2018-01-02 --> users A, B --> 2
2018-01-05 --> users A, B, C --> 3
2018-02-03 --> users A, B, C --> 3
2018-02-08 --> users A, B, C, D --> 4 (same for 2018-02-10, 2018-02-12)
If the user would now select the previous months as range, January should be forgotten, so the expected results would be:
2018-03-02 --> users A, C --> 2
2018-03-08 --> users A, C, D --> 3 (same again true for the rest of the data)
Notice that user B is now missing as user B was only active in January.
I created a solution working for the overall time range with previous() and a marker row and I'm pretty sure that I could solve those issues, but I would need one marker row for each time range the customer uses - and they use a lot: R3, R6, R12, 12 weeks... .
Therefore: has anybody an intelligent generic solution or is one marker row for each time range necessary? Luckily the customer's reference frame is fixed, meaning that in December R3 will be the last completed three months, i.e. Nov, Oct, Sep. . The user is not free to look at the last 3 months starting in April.
Check this out
Script
data: LOAD *, MonthName(Date) as MonthYear; LOAD * INLINE [ Date, User 2018-01-01, A 2018-01-02, B 2018-01-05, A 2018-01-05, C 2018-02-03, A 2018-02-03, C 2018-02-08, D 2018-02-10, A 2018-02-12, C ]; MinDate: LOAD Min(Date) as MinDate Resident data; LET vMinDate = Peek('MinDate'); TRACE $(vMinDate); DROP Table MinDate; AsOfTable: LOAD DISTINCT Date as AsOfDate, MonthName(Date) as AsOfMonthYear, Date(Date - IterNo() + 1) as Date, (IterNo() - 1) as DaysBack Resident data While Date - IterNo() + 1 >= $(vMinDate);
Dimension
AsOfDate
Expression
=Count(DISTINCT {<MonthYear = p(AsOfMonthYear)>} User)
When no selection is made
When you select Feb-2018 in AsOfMonthYear
I think what you need here is The As Of Table
@hico-mah wrote:
This would make date selection easy but will double-count users because Distinct would not work.
Michael -
I don't think that is true... I believe you should still be able to do a DISTINCT count. Let me give it a shot. I am going to try this in QlikView, but it should stay the same for Sense
Check this out
Script
data: LOAD *, MonthName(Date) as MonthYear; LOAD * INLINE [ Date, User 2018-01-01, A 2018-01-02, B 2018-01-05, A 2018-01-05, C 2018-02-03, A 2018-02-03, C 2018-02-08, D 2018-02-10, A 2018-02-12, C ]; MinDate: LOAD Min(Date) as MinDate Resident data; LET vMinDate = Peek('MinDate'); TRACE $(vMinDate); DROP Table MinDate; AsOfTable: LOAD DISTINCT Date as AsOfDate, MonthName(Date) as AsOfMonthYear, Date(Date - IterNo() + 1) as Date, (IterNo() - 1) as DaysBack Resident data While Date - IterNo() + 1 >= $(vMinDate);
Dimension
AsOfDate
Expression
=Count(DISTINCT {<MonthYear = p(AsOfMonthYear)>} User)
When no selection is made
When you select Feb-2018 in AsOfMonthYear
Perfect, I finally got an use case for p in set analysis!