Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
GraemeM
Contributor
Contributor

Rolling 12 Month Bar Chart with Set Analysis

Hello, 

Trying to get a Bar chart of the last rolling year's EBITDA and cannot get it to work ( I would still consider myself as a rookie) 

My Dimension is: %MonthYear in the following format "Jan 2020".

Expression that I am trying to use this board to come up at the moment: 

Sum({<%MonthYear ={">=$(=Date(AddMonths(Max(%MonthYear), -11), 'MMM YYYY')<=$(=Date(Max(%MonthYear), 'MMM YYYY'))"},Year=, Month=>}$(vActEBITDA))

Variable for vActEBITDA: 

RangeSum(
Sum({$<RecordTypeDesc={'Actual'},GLAccountTreeNm4={'1153\Net income (loss)'}>}Amount),
Sum({$<RecordTypeDesc={'Actual'},GLAccountTreeNm6={'1884\Interest expense'}>}Amount),
Sum({$<RecordTypeDesc={'Actual'},GLAccountTreeNm7={'1607\Depreciation & Amortization'}>}Amount))

Let me know if you need actual data to help with this and I can throw together an example app. 

 

 

1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion

let's try this:

sum(
aggr(
rangesum(
above(
Sum( {$< RecordTypeDesc = {'Actual'}, GLAccountTreeNm4 = {'1153\Net income (loss)'} >} Amount )
+
Sum( {$< RecordTypeDesc = {'Actual'}, GLAccountTreeNm6 = {'1884\Interest expense'} >} Amount )
+
Sum( {$< RecordTypeDesc = {'Actual'}, GLAccountTreeNm7 = {'1607\Depreciation & Amortization'} >} Amount )
,
0, 12 ) ), %MonthYear ) )

View solution in original post

5 Replies
agigliotti
Partner - Champion
Partner - Champion

maybe this:

Sum( {$< %MonthYear = {">=$(=Date(AddMonths(Max(%MonthYear), -11), 'MMM YYYY')<=$(=Date(Max(%MonthYear), 'MMM YYYY'))"}, RecordTypeDesc = {'Actual'}, GLAccountTreeNm4 = {'1153\Net income (loss)'} >} Amount )
+
Sum( {$< %MonthYear = {">=$(=Date(AddMonths(Max(%MonthYear), -11), 'MMM YYYY')<=$(=Date(Max(%MonthYear), 'MMM YYYY'))"}, RecordTypeDesc = {'Actual'}, GLAccountTreeNm6 = {'1884\Interest expense'} >} Amount )
+
Sum( {$< %MonthYear = {">=$(=Date(AddMonths(Max(%MonthYear), -11), 'MMM YYYY')<=$(=Date(Max(%MonthYear), 'MMM YYYY'))"}, RecordTypeDesc = {'Actual'}, GLAccountTreeNm7 = {'1607\Depreciation & Amortization'} >} Amount )

GraemeM
Contributor
Contributor
Author

Hmmm, I tried that and got the "No Data to display" error

ThePeterK
Creator
Creator

Did you try a rangesum with total?

93/93
agigliotti
Partner - Champion
Partner - Champion

let's try this:

sum(
aggr(
rangesum(
above(
Sum( {$< RecordTypeDesc = {'Actual'}, GLAccountTreeNm4 = {'1153\Net income (loss)'} >} Amount )
+
Sum( {$< RecordTypeDesc = {'Actual'}, GLAccountTreeNm6 = {'1884\Interest expense'} >} Amount )
+
Sum( {$< RecordTypeDesc = {'Actual'}, GLAccountTreeNm7 = {'1607\Depreciation & Amortization'} >} Amount )
,
0, 12 ) ), %MonthYear ) )

GraemeM
Contributor
Contributor
Author

That did it!  Amazing Thank you!