Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
imark
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.

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)

Capture.PNG

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)
imark
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.

imark
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:
	LOAD
		'$(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.