Measure active users between RegisteredDate and EndDate over time

[Kitty]

Hi there,

I want to create a report that shows my active user count over time. Users are stored in a table where I have dimensions like, CustomerID, RegisteredDate and EndDate. I also have a MasterDates table where I combine all date dimensions from my tables into a Master Calendar. Here's a screenshot of the table where the users are stored and the MasterDates:

Now, I want to create a report which shows me the active users over a period of time from my master calendar. A user is considered active between the PDT_RegisteredDate and the Stripe_EndDate from the PDT table.

Anyone who can help me achieve this?

[hic]

Most likely you need to loop over all days between PDT_RegisteredDate and the Stripe_EndDate  to create a reference date. Then you can simply use this new date as dimension and count the number of UserIDs per date. 

See more on https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944 

[Kitty]

Hi @hic, thanks for your response! I've read through your post and tried to apply it for my use case by adding the following table:

PDT_x_Dates:

Load PDT_UserID,
Date( PDT_RegisteredDate + IterNo() - 1 ) as Period_Date
Resident PDT
While IterNo() <= Stripe_EndDate - PDT_RegisteredDate + 1 ;

But this resulted in a Circular reference... I'm not really sure what I'm doing here and if I'm doing it correctly. Could you advise?

[hic]

You already have a Period_Date in your master calendar, so you need to rename one of the two to something else. See https://community.qlik.com/t5/Qlik-Design-Blog/Circular-References/ba-p/1469332 

Further, you should consider multiple master calendars and/or a canonical date:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578