topic Re: Cumulative sum over dynamic range in App Development

Cumulative sum over dynamic range

hico-mah — Sat, 16 Nov 2024 07:00:39 GMT

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.

Re: Cumulative sum over dynamic range

sunny_talwar — Wed, 19 Dec 2018 12:46:36 GMT

I think what you need here is The As Of Table

Re: Cumulative sum over dynamic range

hico-mah — Wed, 19 Dec 2018 13:05:18 GMT

Hi Sunny, I thought of this too, but I'm afraid that will not fix my problem. This would make date selection easy but will double-count users because Distinct would not work.

cheers

Michael

Re: Cumulative sum over dynamic range

sunny_talwar — Wed, 19 Dec 2018 13:10:33 GMT

@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

Re: Cumulative sum over dynamic range

sunny_talwar — Wed, 19 Dec 2018 13:33:10 GMT

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

Re: Cumulative sum over dynamic range

hico-mah — Tue, 08 Jan 2019 15:47:29 GMT

Perfect, I finally got an use case for p in set analysis!