I have stuck and need some help how to handled this topic? The aim of the task is start showing distribution age of item on the stock.
Master data table contains 12+ mln records with all movement recorded.
So far is ok
I can use DaysinStock to calculate agebucket
output is ok but works only for snapshot for actual day (I mean today())
how can I achieve dynamic graph so when I select "2020-11-05" then the item from the example should be automatically placed in age bucket '<10 days' (age = '2020-11-06' - '2020-10-30' it should be 6 days) but with my current code is still in '61 - 90' (age = '2021-01-08' - '2020-10-30' so 70 days.
the expression which I use works fine only for actual day. When I change [Check stock date] it is not working fully ok
I have managed to do that what I wanted but performance is far far from good (one click on stock date causing 110 sec recalculating on bar chart)
StockDate is from island Table generated from min/max of dates stored in stock table .
Agebucket dimension is also taken from island so I could use pick() function to address the right count into the right bucket frame.
I know the expression is nasty but only this came to my mind and it works for all possible dates. Now the only way to increase speed is to prepare on backend some table with pre calculated age for some possible dates. But it is only workaround as I cant image do this for all 365 days and it is only one year.