Qlik Community

Qlik Sense Cloud Discussions

ndl-kiwi
New Contributor II

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.

Clip.JPG

1 Solution

Accepted Solutions
MVP
MVP

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

How about this?

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

12 Replies
MVP
MVP

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

May be this

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

Employee
Employee

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

ndl-kiwi
New Contributor II

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.

clip 2.JPG

MVP
MVP

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

How about this?

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

ndl-kiwi
New Contributor II

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.

ndl-kiwi
New Contributor II

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

clip 3.JPG

MVP
MVP

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

ndl-kiwi
New Contributor II

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)))

MVP
MVP

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?