Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello world,
So I'm having some trouble with a visualization of a cumulative table.
Background: Imagine a table that controls the stock of a product. It doesn't UPDATE its quantity directly, instead the entire history of the stock of the item is stored (eg. +3 items yesterday and -2 today, now I have 1 item).
When I visualize this table with date filters it doesn't compute the actual quantity of the stock, it calculates the variation of the product during the date I filtered:
Is there any way to sum the quantity of the product up to the date I filtered on Qlik? So if I filtered for 2019, 2020 and 2021, it works out the stock from the first date ever (eg. 2010) up to 2021 and then use that value as a reference for the graph calculation. It would result on something like this:
I'm capable of doing it by creating a table that stores the actual amount of every item yearly and then concatenating with the stock table using a clump of if-statements so it's pretty inefficient. And in that case I wouldn't be capable of changing the filter date as well/I would be obliged to only use yearly filters.
The functions that I'm using for the first graph are:
Dimension: Dual(Year([Datejoin])&'-'&Month([Datejoin]),MonthStart([Datejoin]))
Measure: RangeSum( above(total sum( {$<Type={'Standard'}, IsPurchaseItem={'1'}, IsSalesItem={'1'}>} [Stock.StockPosition.Amount]),0,RowNo(total)))
Thank you!
Bump. Does anyone know?
I tried working this out in the Data Load Editor but I can't interact with the filter as a variable there.
I decided to experiment on a KPI just for debugging purposes but the result should be the same. On the expression this is what I'm kinda working with (2010 is the starting point where I should start looking):
sum({$<Type={'Standard'}, IsPurchaseItem={'1'}, IsSalesItem={'1'}, [Datejoin.autoCalendar.Year] = {$(=Date(AddYears(Min([Datejoin.autoCalendar.Year]), 2010-Min([Datejoin.autoCalendar.Year])),'YYYY'))} >} [Stock.StockPosition.Amount])
I can try to explain it better if needed.