Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a combo chart with bar charts showing delta's actual Vs Budget per month.
I want to add a line showing the cumulative differences for the year.
As a newbie I tried with Aggr(RangeSum(Above several times and based upon different suggestions I found in the forum. However I can't make it work.
I'm using below expresion for the delta calculation:
(-Sum({<CY={'1'}, [AccCode] = {2} ,[P&L/Balance]={'P&L'}>}-[#Actual]) +
Sum({<CY={'1'}, [AccCode] -= {2} ,[P&L/Balance]={'P&L'}>}[#Actual]))/1000
-
(-Sum({<CY={'1'}, [AccCode] = {2} ,[P&L/Balance]={'P&L'}>}-[#Budget]) +
Sum({<CY={'1'}, [AccCode] -= {2} ,[P&L/Balance]={'P&L'}>}[#Budget]))/1000
Anyone able to help me out on how to create a cumulative line for these delta's?
I tried starting above with :
Aggr(RangeSum(Above
& ending with:
, 0, RowNo())), ($(vJYearMonthNr), (NUMERIC)))
Something like this?
This is what I used
=Aggr(
RangeSum(Above(TOTAL
Sum({<cytd = {'1'}, acccode = {'2'}, [b/p&l] = {'P&L'}>} actual) - Sum({<cytd = {'1'}, [acccode] = {'2'}, [b/p&l] = {'P&L'}>} [budget])
, 0, RowNo()))
, $(='['& vYearMonthNr & ']'))
What is your variable vJYearMonthNr here? Is it giving a field or expression?
This variable is giving a field.
May be try this
$(='[' & $(vJYearMonthNr) & ']')
or
$(='[' & vJYearMonthNr & ']')
Hi Sunny,
Thanks for your suggestion.
However. Unfortunately both suggestions don't seem to work and results in not showing any data at all. Is there any other way to get the cumu line in this graph?
Would you be able to share a sample to look at the issue?
Unfortunatly I am not able to share the original file.
I recreated some data in an excel file and created the visualisation. In the formula editor I incorporated several tries and suggestions but also don't seem to work in this test file.
Something like this?
This is what I used
=Aggr(
RangeSum(Above(TOTAL
Sum({<cytd = {'1'}, acccode = {'2'}, [b/p&l] = {'P&L'}>} actual) - Sum({<cytd = {'1'}, [acccode] = {'2'}, [b/p&l] = {'P&L'}>} [budget])
, 0, RowNo()))
, $(='['& vYearMonthNr & ']'))
Yes, Thanks so much! This works