Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

expression problem

hi there,

i am struggling with a chart expression.given the following chart:

MonthYearRevenueCumulated RevenueCOMMENT
JAN2015100100=JAN
JAN20165050=JAN
JAN2017200200=JAN
FEB201520120=JAN+FEB
FEB2016200250=JAN+FEB
FEB201710210=JAN+FEB
MAR201560180=JAN+FEB+MAR
MAR201620270=JAN+FEB+MAR
MAR201750260=JAN+FEB+MAR

what i want is aggergate every month's revenue + the previous month's revenue for every year --> column [CUMULATED REVENUE]

any help how i can do this ?

thanks a lot!

6 Replies
Highlighted

Re: expression problem

May be like this

Aggr(RangeSum(Above(Sum(Revenue), 0, RowNo())), Year, Month)

Highlighted
MVP
MVP

Re: expression problem

Maybe use an AsOf table like shown in

The As-Of Table

Highlighted
Not applicable

Re: expression problem

Thanks. But I should say that i cant / dont want to modify the script....

Highlighted
MVP
MVP

Re: expression problem

Then use something like Sunny suggested or something like described here:

Calculating rolling n-period totals, averages or other aggregations

If you go for the Aggr() solution, take care that your Month show chronological load order or use the sorted aggr() dimensions:

The sortable Aggr function is finally here!

Highlighted

Re: expression problem

Here is a sample attached

Capture.PNG

But like Stefan mentioned, this solution assumes that your Month field is sorted in the ascending order in the script. If it isn't you will either need to sort this in the script or you can use The sortable Aggr function is finally here!‌ in QV12 or above. The syntax would look like this

Aggr(RangeSum(Above(Sum(Revenue), 0, RowNo())), Year, (Month,(NUMERIC)))

Highlighted
Specialist II
Specialist II

Re: expression problem

Check the attachment