Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
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.