Cumulative sum in line chart for only 3 years

[David Boot-Olazabal]

Hi,

 

I have a question regarding the creation of a cumulative sum expression in a line chart.

My graph displays 10 years of data, showing actual costs , budgetted costs and expected costs. When I create a line chart, I use this expression to create cumulative sums for all cost amounts: Sum([sv_cost_amount]) THEN Sum([Value]) OVER (AllPrevious([Axis.X])).

My three cost figures are perfectly represented as three different lines running from 2017 to 2027. But my actual costs are a flat line after 2019, showing the same amount in the period 2020-2027 as the value of 2019.

Is there any possibility that I can cut off the line As there are no actual costs yet for the 2020-2027 period, I prefer not to show the 'flat line'. I just want to show only the cumulative actual costs for the years 2017, 2018 and 2019. The other two costs figures (budgetted and expected) can stay until 2027, but my actuals should stop in 2019. I have tried different OVER aggregation expressions, but each expression didn't work out.

So if there is a solution (or it's just impossible), I'd really like to know.

Thanks,

David Boot-Olazabal

[Shashank Gurnalkar]

Hello David,

 

I don't think it is possible to trim the line from 2020 onwards with the other two lines still being evaluated till 2027. One workaround could be to have the actual line in separate visualization and limit its data till the year 2019.

[David Boot-Olazabal]

Hi Shashank,

I ended it up creating a case with Support. The only possible way to do this, is to create your own cumulative expression using a case when statement. It's a bit messy though, but this is what I ended up with:

Avg(Case

When [year]=2017 then [2017 ACT]

When [year]=2018 then [2017 ACT] + [2018 ACT]

When [year]=2019 then [2017 ACT] + [2018 ACT] + [2019 ACT]

else null end)

I now see the 3 lines correctly added up in my visualization, having my Actuals only display 3 years. I need to finetune this as it is hardcoded and using other variables as well, but for now, this is the basis we can work with.

Kind regards,

David Boot-Olazabal

[Shashank Gurnalkar]

That's great :)