# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
New Contributor II

## Running count of distinct users over n-period

Hello everyone;

my dataset looks something like this

```InlineTable:LOAD * INLINE [
Date, User
01/01/17, u1
01/01/17, u2
02/01/17, u1
03/01/17, u3
03/01/17, u5
04/01/17, u3
04/01/17, u1
05/01/17, u1
05/01/17, u7
05/01/17, u8
06/01/17, u2
06/01/17, u4
];```

Based on it I have to generate three measures:

the number of daily active users (a straightforward count distinct) and the number of distinct active users over an n-period. For the sake of simplicity and to be able to play with my example let's say over 2 and 4 days prior (including current).

Taking all three measures for 06/01/17 should yield:

daily active: 2 (u2, u4)

date-to-2prior: 6 (u1,u2,u3,u4,u7,u8)

date-to-4prior: 7 (as above plus u5)

In turn, for 04/01/17 we should get:

2

3

NULL (because by the 4th we still don't have 4 days prior, only 3).

I tried to fiddle around with RangeSum and RangeCount but couldn't make any sense out of them. Any help here will be greatly appreciated.

Labels (3)

3 Replies

## Re: Running count of distinct users over n-period

PFA

='daily active : ' & Chr(10) & Count({<Date={"\$(=Max(Date))"}>} DISTINCT User)

='date-to-2prior : ' & Chr(10) & Count({<Date={">=\$(=Date(Max(Date-2)))"} >} DISTINCT User)

='date-to-4prior : ' & Chr(10) & Count({<Date={">=\$(=Max(Date-2))<=\$(=Max(Date))"}>} DISTINCT User)

='In turn : ' & Chr(10) & Count({<Date={"\$(=Date(Max(Date-2)))"}>} DISTINCT User)

='Null : ' & Chr(10) & Count({<Date={"<\$(=Date(Max(Date-2)))"}>} DISTINCT Date)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
New Contributor II

## Re: Running count of distinct users over n-period

Thank you, this is a neat solution, but I can tell that it will only show the data  based on the last day in the sample.

My fault for not making it explicit in the question but I need an expression that applies the logic to each day in the table.

New Contributor II

## Re: Running count of distinct users over n-period

In the end I managed to solve it by mustering the help of master_calendar and a FOR loop:

```FOR w = 0 TO NoOfRows('master_calendar')-1
LET vDate = PEEK('Date',\$(w),'master_calendar');

FinalTable:
'\$(vDate)'									AS ft_date
,dim1
,dim2
,dim_etc
,COUNT(DISTINCT IF('\$(vDate)' - DATE(Date, 'YYYY-MM-DD') = 0	, User))	AS Distinct_on_date
,COUNT(DISTINCT IF('\$(vDate)' - DATE(Date, 'YYYY-MM-DD') < 7	, User))	AS Distinct_last_7d
,COUNT(DISTINCT IF('\$(vDate)' - DATE(Date, 'YYYY-MM-DD') < 30	, User))	AS Distinct_last_30d
RESIDENT InlineTable
WHERE '\$(vDate)' - Date >= 0
AND '\$(vDate)' - Date < 30
GROUP BY
'\$(vDate)'
,dim1
,dim2
,dim_etc
;
NEXT;```

Basically for each day found in master calendar the script above will load the dates where the difference (between calendar date and date in InlineTable) is in the range from 0 to 29. The COUNT(DISTINCT ... ) will then do the trick of finding the number of distinct users within any set interval (as long as it is within the boundaries set in the WHERE clause).

Still, if you can think of solutions that could be executed within a chart let me know.