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:
- If a user selects Year-Week, opening stock, closing stock, the opening stock should show stock at the first date of the week, and closing stock on the end of the week, for each week visible in the object.
- If a user selects Year-Month, opening stock, closing stock, the opening stock should show stock at day 1 of the month, and closing stock should show at the last day of the month, for each year-month visible.
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 |
Qlik Sense Business
