I work a lot with clients that offers subscription-based products and want to track the product and customer stock over time. Often they are happy with monthly numbers, but there are also a lot of times where they want to drill-down on specific days to see what happened during certain campaigns, outside events etc.
The limitation we see in Qlik Sense is that there is currently no good way to track this on both a historical and granular way in the same application. There are ways to do it, but all of them have their limitations:
- Slowly changing dimension table with IntervalMatch() - This works perfectly up until a certain limit, when the number of rows generated by the intervalmatch function simply drains the performance to much. This is still the best current solution if you manually create it and stagger the interval (data further back is not on daily basis e.g.)
- Event-based table (one row per event such as "Churn", "Purchase" etc.) and use of rangesum - This works good in isolation, but has a major issue that it's not date-aware, just row/column-aware, so you can't re-use measures between objects without weird results. It is also very sensitive to user filtering and unusable in self-service objects.
- Snapshot tables (monthly/weekly/quarterly status per subscription) - This works well as an overview, but you lose all the details. For a lot of cases it also generates a lot of unnecessary data rows since nothing happens on a subscription between most snapshots.
Suggested Solution:
Create date-aware functions or allow you to specify which fields to use range sum on. This would allow users to create time-aware stock data based on an event table.
Stock would then be RangeSum([Product Stock Measure],[Event_Date],Full) to accumulate product stock over time, no matter what dimension is in the charts. This would allow me to use the same measure when displaying overall stock in a line graph on a daily basis as when I set it up in a pivot table with Product on one axis and week on the other.
Qlik Sense Business
