topic Re: Cumulative sum using rangesum function in QlikView

Cumulative sum using rangesum function

sadiaasghar — Wed, 25 Nov 2020 16:16:04 GMT

Simple sum Expression is working but when I apply rangesum formula to caculate Cumulative it's returning 0.

Sum Expression:

SUM(TOTAL<[Invoice Date_FKDAT]>aGGR(((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}
>}[SalesMTon])))
-
((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))},
[Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2),[Invoice Date_FKDAT]))

Cumulative sum Expression:

=Rangesum(above(total
(fabs(Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}>}[SalesMTon])))
-
(((fabs(Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))},[Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2),0,RowNo(total)))

Re: Cumulative sum using rangesum function

sadiaasghar — Thu, 22 Oct 2020 04:07:21 GMT

@sunny_talwar @Kushal_Chawda

Re: Cumulative sum using rangesum function

sunny_talwar — Thu, 22 Oct 2020 16:17:44 GMT

@sadiaasghar Sum expression and cumulative sum expression within RangeSum(Above()) looks different. For example, you have TOTAL and Aggr in sum expression, but not in RangeSum(Above())). May be try this

RangeSum(Above(TOTAL SUM(TOTAL<[Invoice Date_FKDAT]>aGGR(((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))} >}[SalesMTon]))) - ((((Sum({<RecType={'Sales'},[Posting Status_RFBSK]={'C'},[FISCAL YEAR] = {$(=Max([FISCAL YEAR]))},[MonthNo] = {$(=Match(Month(Date(Max([Invoice Date_FKDAT]))), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))}, [Billing Type_FKART]={'S1','RE','ZRAX','ZREX','ZRFX','ZRFI','G2','ZRCO'}>}[SalesMTon]))))*2),[Invoice Date_FKDAT])) , 0, RowNo(TOTAL)))

Re: Cumulative sum using rangesum function

sadiaasghar — Thu, 05 Nov 2020 04:28:47 GMT

@sunny_talwar it's returning 0 as cumulative. It didn't solve my problem.