Categories
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?
Answers
-
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'?
0 -
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.
0 -
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?
0 -
The simplest way that I know of is to use a a pivot chart, which gives you the option: "Display as Running Sum".
0 -
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!
0 -
It is the same, there is a display control which allows you to show graph (with table or instead of table)
0 -
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.
0 -
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...
0