Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

We value your feedback – take our 5-minute QlikView modernization survey
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Running count of distinct users over n-period

Hello everyone;

my dataset looks something like this

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:



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


='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)
Contributor III
Contributor III

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.

Contributor III
Contributor III

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');
		'$(vDate)'									AS ft_date
		,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

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.