cumulative sum in Qlik Sense

[ali_hijazi]

Hello I'm working on a pivot table in Qlik Sense

the dimensions are Name1, Name2, and Month_Year

the expression is very simple say sum(Amount)

what I want to display is the following:

in Jan the sum of amount in Jan

under Feb I want to display the sum of amount in Feb + Amount in Jan

under Mar display amount of Jan + amount of Feb + amount of Mar

Please advise on how to accomplish this

[sunny_talwar]

If Month_Year is the pivoted, then may be this:

RangeSum(Above(TOTAL Sum(Amount), 0, ColumnNo(TOTAL)))

[kaushiknsolanki]

Hi,

Hope this link will give you guidance.

Accumulation in Qlik Sense

Regards,

Kaushik Solanki

[ali_hijazi]

good this works for some

but Now I want to calculate a ratio which is Gross Margin / Sales

in Jan it is Gross Margin in Jan over Sales in Jan

in Feb it is the gross margin in Jan and Feb over Sales in Jan and Feb

I wrote this in my expression:

(rangesum(before(TOTAL $(vPL_Amount), 1, ColumnNo(TOTAL))) + $(vPL_Amount))

+

rangesum(before(TOTAL $(vPL_GrossMarginAmount), 0, ColumnNo(TOTAL)))

/

rangesum(before(TOTAL $(vPL_Sales_Amount), 0, ColumnNo(TOTAL)))

the first line in bold works just fine when alone

the last two lines work just fine when alone

however when I put all of above expression I the first line returns zeros

please advise why

[ali_hijazi]

please check attached QVF

go to Profit and Loss and select between Monthly and YTD option

[sunny_talwar]

Try this (Changes highlighted in red)

RangeSum(

RangeSum(Before(TOTAL $(vPL_Amount), 1, ColumnNo(TOTAL))),

$(vPL_Amount)),

RangeSum(Before(TOTAL $(vPL_GrossMarginAmount), 0, ColumnNo(TOTAL))))

/

RangeSum(Before(TOTAL $(vPL_Sales_Amount), 0, ColumnNo(TOTAL)))

[sunny_talwar]

Where do I need to select between Monthly and YTD option?

[ali_hijazi]

Ah I'm using extension here

I will send you the extension when I'm back to office

[agigliotti]

Currently i'm using the below expression in qlik sense table:

RangeSum( Above( column(2), 0, RowNo() ))

but I get 0 as grand total.

Total function is set to Auto.

How can I get the right grand total ?

Many thanks in advance.

BR

Andrea