topic Re: Rolling 12 Month Bar Chart with Set Analysis in QlikView

Rolling 12 Month Bar Chart with Set Analysis

GraemeM — Thu, 13 Feb 2020 15:08:03 GMT

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.

Re: Rolling 12 Month Bar Chart with Set Analysis

agigliotti — Thu, 13 Feb 2020 15:21:45 GMT

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 )

Re: Rolling 12 Month Bar Chart with Set Analysis

GraemeM — Thu, 13 Feb 2020 15:28:55 GMT

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

Re: Rolling 12 Month Bar Chart with Set Analysis

ThePeterK — Thu, 13 Feb 2020 15:48:23 GMT

Did you try a rangesum with total?

Re: Rolling 12 Month Bar Chart with Set Analysis

agigliotti — Thu, 13 Feb 2020 15:59:02 GMT

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

Re: Rolling 12 Month Bar Chart with Set Analysis

GraemeM — Thu, 13 Feb 2020 16:27:12 GMT

That did it! Amazing Thank you!