topic How to create a cumulative value. in QlikView

How to create a cumulative value.

vikasshana — Mon, 29 Aug 2022 11:14:43 GMT

Hi,

I've the below data in my app.

Table:
Load Date(Date#(Date,'MM/DD/YYYY')) as Date, Sales Inline [
Date, Sales

11/26/2020, 400

11/26/2021, 300
12/26/2021, 100
1/1/2022, 200
];

In the front end I want to load only data from previous year until current year previous month. I'm using the below expressions

Dimension: Date(monthstart(aggr(only({<Date = {">=$(=Date(Yearstart(today(),-1)))<=$(=Date(MonthEnd(today(),-1)))"}>}Date),Date)),'YYYY-MM')

Expression: RangeSum(Above(Sum(Sales),0,RowNo()))

Output:

Date	Value
2021-11	300
2021-12	400
2022-01	600

Required Output

Date	Value
2021-11	700
2021-12	800
2022-01	1000

@PrashantSangle

Re: How to create a cumulative value.

BrunPierre — Mon, 29 Aug 2022 18:12:42 GMT

@vikasshana Maybe like this

Dimension: Date(Date,'YYYY-MM')

Expression: Sum({<Date={">=$(=AddMonths(max(Date),-11))"}>}Aggr(RangeSum(Above(Sum({<Date=>}Sales),0,11)),Date))

Re: How to create a cumulative value.

vikasshana — Tue, 30 Aug 2022 06:50:24 GMT

I've tried your solution, but still the same

Re: How to create a cumulative value.

BrunPierre — Tue, 30 Aug 2022 07:45:45 GMT

With the same sample date, this is the result. Could be the date format.

Re: How to create a cumulative value.

vikasshana — Tue, 30 Aug 2022 08:01:24 GMT

I'm using the below sample code where I'm changing the date format, but still the same result

Table:
Load Date(Date#(Date,'MM/DD/YYYY')) as Date, Sales Inline [
Date, Sales
11/26/2020, 400
11/26/2021, 300
12/26/2021, 100
1/1/2022, 200
];

Re: How to create a cumulative value.

BrunPierre — Wed, 31 Aug 2022 22:56:54 GMT

Sum({<Date={">=$(=AddMonths(max(Today()),-11))"}>}Aggr(RangeSum(Above(Sum({<Date=>}Sales),0,11)),Date))

Re: How to create a cumulative value.

vikasshana — Thu, 01 Sep 2022 10:40:11 GMT

Thanks for that @BrunPierre , this expression is working with the sample data but when I use the same expression in my main application it is not working.