Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hico-mah
Partner - Contributor II
Partner - Contributor II

Cumulative sum over dynamic range

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.

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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

image.png

When you select Feb-2018 in AsOfMonthYear

image.png

View solution in original post

5 Replies
sunny_talwar

I think what you need here is The As Of Table

hico-mah
Partner - Contributor II
Partner - Contributor II
Author

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
sunny_talwar


@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

sunny_talwar

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

image.png

When you select Feb-2018 in AsOfMonthYear

image.png

hico-mah
Partner - Contributor II
Partner - Contributor II
Author

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