Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
PJAH
Contributor
Contributor

Cumulative Value over periods in Combo Chart

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

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

Something like this?

image.png

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 & ']'))

View solution in original post

8 Replies
sunny_talwar

What is your variable vJYearMonthNr here? Is it giving a field or expression?

PJAH
Contributor
Contributor
Author

This variable is giving a field.

sunny_talwar

May be try this

$(='[' & $(vJYearMonthNr) & ']')

or

$(='[' & vJYearMonthNr & ']')
PJAH
Contributor
Contributor
Author

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?

sunny_talwar

Would you be able to share a sample to look at the issue?

PJAH
Contributor
Contributor
Author

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.

sunny_talwar

Something like this?

image.png

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 & ']'))
PJAH
Contributor
Contributor
Author

Yes, Thanks so much! This works