Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please share your data or app.
Thank you!
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?
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]
;