Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Highlighted
Partner
Partner

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
Highlighted

Re: Cumulative sum over dynamic range

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
Highlighted

Re: Cumulative sum over dynamic range

I think what you need here is The As Of Table

Highlighted
Partner
Partner

Re: Cumulative sum over dynamic range

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
Highlighted

Re: Cumulative sum over dynamic range


@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

Highlighted

Re: Cumulative sum over dynamic range

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

Highlighted
Partner
Partner

Re: Cumulative sum over dynamic range

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