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: 
alextomlins
Contributor III
Contributor III

Active users from Start and End Date

Hi Guys,

I have been having some trouble. I have a table called CustomerSubscriptions and this has data that can be boiled down to

Customer a, Product X, Manufacturer Y, StartDateTime, EndDateTime.

Customer b Product Y, Manufacturer X,StartDateTime, EndDateTime.
etc

I want to be able to take the start date and end date and visualise a count of customers that were Live in reporting Months/Weeks/Days.

I don't know if this is best done in the load or as a UI expression but it is difficult because i cannot have a month or day row for every month for every customer and so the it would need to dynamically work out whether the user rows were active depending on what dimensions splits were visualised on a graph. showing several counts of active subscribers per reporting month, week day etc.

I have struggled with this for some time.

Basically we need to have something that counts every customer's live period if in the dimension splits

3 Replies
isingh30
Specialist
Specialist

Please share your data or app.

Thank you!

alextomlins
Contributor III
Contributor III
Author

I might be able to send a table with some personal details removed but generally I  can't  send any data if it's possible to explain the process or code snippets I would really appreciate It. Really though it's as I said customer I'd, product, manufacturer, startdatetime and end date time.

It Is ok ked to a master calendar and there is a composite key involving product and manufacturer. I've simplified it because it's more the approach I'm after. It seems like it kgiht be something I need to do in an expression but maybe it's possible in the load. Trhe issue is that the data needs to be calculated dependent on the time dimension selected. So that to me says expression?

dwforest
Specialist II
Specialist II

Easiest way to me is to use IntervalMatch and create a flag for each date indicated a current customer for that date; then any slice of time would have a flag and can be rolled up to week, month, quarter, year.

to your table, something like:

JOIN

IntervalMatch (Date)

LOAD StartDateTime, EndDateTime.

RESIDENT [CustomerTimes]

;   

JOIN

LOAD Customer,

     Date,

     1 as Active

RESIDENT [CustomerTimes]

;