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