12 Replies Latest reply: Oct 25, 2017 8:37 PM by Fabiana Iglesias

# Accumulated SUM of Revenue, Comparing two or more years

Hi,

In qlikview it is really easy, but in Qlik Sense Cloud I'm struggling to accumulate revenue month by month, and having more than one year in the Line Chart.

I have found that rangesum( above(total sum(revenue),0,rowno(total))) accumulates perfectly when I'm only selection one year. But if I remove the selection and include 3 years, they show as three different colors (which is perfect) but accumulate wrongly. I need each year to start the year from zero in the accumulation.

The reason for the yellow line being so short is that we have no revenue data prior to September 2015. Blue line is 2017, where August is latest data I have.

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

May be this

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

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

Thank you for your help. Replacing my original expression with your proposal generates the following chart instead. Now the 2015 graph is correct. Bu the two others doesn't start from zero - The two other are wrong - not starting at zero and definitively not aggregated (there are no negative values in the data, so line has to go upwards).

I'm really puzzled how to get this done. I did try to play with Calendar Measures as per below, where I can get absolute numbers displayed, but I cant get then into a graph.

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

Aggr(RangeSum(Above(Sum(revenue), 0, RowNo())), Year, (Month, (Numeric)))

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

Amazing! I just spend 4 hours finding a solution, and finally gave up and asked the community. And you solved it! Fantastic! Thank you so much.

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

Ups - I was a little bit too quick there. While they all accumulate from zero now, year 2017 includes 2016 figures, and 2016 includes 2015 figures. So it is still not 100% correct, but I'm sure you can easily point it out.

So the below is what it shows right now. August as an example:

2015: Value 0

2016: Value appx 4.4

2017: Value appx 8.8

Graphs shows August 2017 to be 13.2 - and not the correct 8.8

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

What is the expression you are using? Is there a TOTAL in there? You don't need TOTAL

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

I'm suing this one:

Aggr(RangeSum(Above(Sum(Revenue), 0, RowNo())), [activity_date.autoCalendar.Year], ([activity_date.autoCalendar.Month], (Numeric)))

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

May be this is a function of area chart? Do you still see this 13.2 for Aug 2017 when you convert this into line chart or a table?

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

I was indeed. Deselection of "Stacked Area" solved the problem. Thank you again for your help.

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

No problem at all

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

Thank you Sunny,

You´ve helped me too.

You´re amazing!!!

• ###### Re: Accumulated SUM of Revenue, Comparing two or more years

Hello Nicolai - thanks for your question - it would be helpful to know also - if the Calendar Measures feature helps you with this - are you aware of this feature? If not check it out here Creating calendar measures - Qlik Sense - YouTube and let us know.

Thanks

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Mike

Qlik