Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
DanielPeixoto
Contributor
Contributor

Visualization of a cumulative table with date filters

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:

1.jpg

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:

2.jpg

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!

Labels (2)
1 Reply
DanielPeixoto
Contributor
Contributor
Author

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.