Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table that shows the daily product stock for each product, customer segment etc. something like this:
Date | product | Segment | Opening Stock | Closing Stock |
2023-09-14 | Product A | segment x | 15 | 18 |
2023-09-14 | Product B | segment x | 5 | 5 |
2023-09-15 | Product A | segment x | 18 | 17 |
2023-09-15 | Product B | segment x | 5 | 10 |
the users want to analyse this data on a variety of date aggregations (weekly, monthly, quarterly etc.), so I want to have the option to select different granularities. The issue I am having is that since this is an non-additive measure I can't just add sum() and call it a day, but rather for each date selection I want to show the stock at the opening of the selection and end of selection.
For total kpi values this is not an issue since it's just a set of <date = {$(max/min(date))}> , but I can't figure out how to do it in more dynamic objects such as the Vizlib custom report.
What I want to achieve:
Something like this:
Year-Week | Opening Stock (take from 2023-01-02, 2023-01-09 etc.) | Closing Stock (take from 2023-01-08, 2023-01-15 etc.) |
2023-01 | 1 | 3 |
2023-02 | 3 | 8 |
2023-03 | 8 | 7 |
2023-04 | 7 | 10 |
for year month it would be
Year-Month |
Opening Stock (take from 2023-01-01, 2023-02-01 etc.) |
Closing Stock (take from 2023-01-31, 2023-01-28 etc.) |
2023-01 | 1 | 10 |
2023-02 | 10 | 15 |
2023-03 | 15 | 21 |
2023-04 | 21 | 20 |