8 Replies Latest reply on May 22, 2018 2:05 PM by Robert Angel

Running SUM for each year on the graph

Hi,

In last weeks I have already asked few questions.

Maybe this time also someone will be able to help me.

Requirement is to have a line graph per month for last 3 years presented as running sum. Previous 3 years should be from Jan till Dec. Current to date.

I have tried several options but didn't manage to find solution.

Analysis has filter on day level and "standard" measure used. It ends as such viz. I have also measure YTD created in RPD, but if it is used, scale hit the sky (I assume that in such case each day is single YTD point, and results are somehow multiplied). I have tried to use RSUM by Year, but it doesn't have any effect (it is not passed in SQL).

As it is line graph, not possible to duplicate layer and set as Running Sum.

What else can I check?

What do I need to change and where to have required visualization?

• 1. Re: Running SUM for each year on the graph

I could be misunderstanding your requirement and grossly simplifying, but have you tried in the formula using sum by (year in your case)?

Also, when you say the numbers go crazy, have you tried changing the formula control to 'server complex aggregate'?

• 2. Re: Running SUM for each year on the graph

Hi Robert.

SUM By Year ends with straight line for each year which is the sum of the month for each month. Server complex aggregate do not change the scale. Still from 0 to 600k.

• 3. Re: Running SUM for each year on the graph

By sum by year I mean; -

sum (your_measure_here by Your_Year_Dimension_Here)

Without the graph, straight table, do your results look valid?

• 4. Re: Running SUM for each year on the graph

The simplest way that I know of is to use a a pivot chart, which gives you the option: "Display as Running Sum".

• 5. Re: Running SUM for each year on the graph

I know where the Pivot Table view is. But where is Pivot graph?

EDIT: Good to learn something new.

I must say that I didn't new that in Pivot Table view, you can add also graph.

Works great!

• 6. Re: Running SUM for each year on the graph

It is the same, there is a display control which allows you to show graph (with table or instead of table)

• 7. Re: Running SUM for each year on the graph

The pivot charts are usually my first option, because I've found them to be more flexible and adaptable.   In the position selector you can choose "chart only" to hide the actual pivot table.

• 8. Re: Running SUM for each year on the graph

Have to agree with Jerry, sometimes the graphs in pivot table view are more flexible than their equivalents outside of pivot table, my only concern with pivot tables generally is they put a lot of the burden of processing on the client and hence aren't as scaleable...