Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
imark
Contributor III
Contributor III

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
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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.