# Qlik Sense App Development

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

## 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)

• ### dynamic

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Cumulative sum over dynamic range

Check this out

Script

```data:
MonthName(Date) as MonthYear;
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:
Resident data;

LET vMinDate = Peek('MinDate');
TRACE \$(vMinDate);
DROP Table MinDate;

AsOfTable:
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 you select Feb-2018 in AsOfMonthYear

5 Replies
Highlighted
MVP

## Re: Cumulative sum over dynamic range

I think what you need here is The As Of Table

Highlighted
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
MVP

## 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
MVP

## Re: Cumulative sum over dynamic range

Check this out

Script

```data:
MonthName(Date) as MonthYear;
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:
Resident data;

LET vMinDate = Peek('MinDate');
TRACE \$(vMinDate);
DROP Table MinDate;

AsOfTable:
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)`