Visualization of a cumulative table with date filters

DanielPeixoto — Tue, 30 Nov 2021 18:17:33 GMT

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!

Re: Visualization of a cumulative table with date filters

DanielPeixoto — Tue, 10 Aug 2021 11:23:11 GMT

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.

topic Visualization of a cumulative table with date filters in App Development

Visualization of a cumulative table with date filters

Re: Visualization of a cumulative table with date filters