topic Re: Aging on the selected day in QlikView

Aging on the selected day

waszcma1 — Fri, 08 Jan 2021 21:15:29 GMT

Hi all,

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.

example:

Script:

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

count(distinct{< FirstTimeIN={"<=$(=only([Check stock date]))"} >} %KEY_ACTUAL)

Re: Aging on the selected day

MayilVahanan — Sun, 10 Jan 2021 02:09:05 GMT

HI @waszcma1

If you want dynamic o/p, you need to calculate the Daysinstock / aging in front end instead of script.

Instead of Today, you can use max( [Check stock date]) in front end.

use variable, for easy approach.

Re: Aging on the selected day

waszcma1 — Mon, 11 Jan 2021 21:20:27 GMT

Hi,

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.

Can you advice any other approach?