topic Re: Cumulative Sum Based on Dimensions in QlikView

Cumulative Sum Based on Dimensions

Mon, 02 Mar 2015 18:29:19 GMT

Hello, i was searching for answers to a problem I'm trying to solve, but I could not do it with what I found. So here is my question.

I have 3 dimensions, Filial, Data and Codigo_Produto. For those 3, there is the field QTDE_ENTRADA, that can be seen on the fourth column ( entradas ). This column is a basic sum ( Sum(QTDE_ENTRADA) ).

Then I have the last column that should be a cumulative column. Basically, it should respect the FILIAL and CODIGO_PRODUTO columns and consider all the previous or equal date in DATA column.

So for example, the third line should be a sum considering FILIAL and CODIGO_PRODUTO with all the values <= DATA field. So, since third line DATA is "18/12/2014", it should cumulate everything equals or previous than itself (18/12/2014,12/09/2014,12/04/2014)

Second line should cumulate itself and previous dates in DATA columns as well (12/09/2014,12/04/2014)...

I don't know if I could make myself clear, I apologize for my English.

Thanks in advance for the help.

Re: Cumulative Sum Based on Dimensions

Gysbert_Wassenaar — Mon, 02 Mar 2015 18:38:55 GMT

Demote the DATA dimension to last place (i.e. to the third column) and use rangesum(aggr(sum(QTDE_ENTRADA),0,rowno())) as expression.

Re: Cumulative Sum Based on Dimensions

JonnyPoole — Mon, 02 Mar 2015 18:40:40 GMT

How about the following to add up current and previous rows ? sum(Sales) is the basic expression

rangesum( above( sum(Sales),0,RowNo()))

Re: Cumulative Sum Based on Dimensions

Mon, 02 Mar 2015 18:59:12 GMT

Hi Gysbert, I tried this one but it resulted on 0 for the whole column. I just demoted the column as you told me and tried to use expression.

Re: Cumulative Sum Based on Dimensions

Mon, 02 Mar 2015 19:03:08 GMT

Hi, Jonathan. Uhm... not sure if I can count on current and previous rows to sum. For example let's suppose I filter my table by an specific date on DATA column, the third for example. Cumulative column should still give me 6. with this expression, the result resets to 2, which is it's own value only, and not a cumulative answer. Is there anyway to cumulate even when filtering ? Thanks

Re: Cumulative Sum Based on Dimensions

JonnyPoole — Mon, 02 Mar 2015 20:51:34 GMT

Ok. I made an adjustment to disregard a specific filter. Here is the if() expression combined with the rangesum()

if( substringcount(Concat(Year,'-'),Year),

rangesum( above( sum( {$<Year=>} Sales),0,RowNo())))

Re: Cumulative Sum Based on Dimensions

alibd225 — Thu, 06 Aug 2015 06:40:01 GMT

In acceding order it's working fine, but in in descending order it's not working.

please help me to in descending order because we have 10 years information and i need to display last year cumulative value

Re: Cumulative Sum Based on Dimensions

venkatesan_e — Tue, 05 Feb 2019 16:20:44 GMT

Hi Team,

I have problem with cumulative sum for multiple dimension,

YearMonth,SKU group,Sku and etc.

for 1st month same value, when coming to 2nd month cumulative no previous month skugroup and SKU.

Yearmonth, Counts, Cumulative

01 10000, 10000

02 5000,15000

03 2000,17000

Cumulative will happen based on skugroup, SKU - month on Month.

if you have time can you help in this in script level