topic Re: Rolling 12-Months sum in QlikView

Rolling 12-Months sum

shamsu_mk — Wed, 25 Nov 2020 16:16:04 GMT

Dear Team

I have EBITDA data on monthly basis. I would like to have an expression field for rolling-12 months sum in the field R-Sum2.

Please support. Attached the data file

Shamsu

Re: Rolling 12-Months sum

Anil_Babu_Samineni — Sun, 10 Sep 2017 13:35:21 GMT

How about this?

Sum({<Month = {">=$(=AddMonths(Max(Month),-12)) <=$(=Max(Month))"}>} EBITDA)

Re: Rolling 12-Months sum

effinty2112 — Sun, 10 Sep 2017 15:18:55 GMT

Hi Shamsu,

For a rolling sum make a straight table with the expression you want to calculate the rolling sum on. Have the Year and Month dimensions in the table and/or the period dimension and make sure the table is sorted correctly. Go into the Expressions tab and for your expression choose Accumulate 12 steps back.

Good luck

Andrew

Re: Rolling 12-Months sum

sunny_talwar — Sun, 10 Sep 2017 23:39:48 GMT

May be this?

RangeSum(Above(TOTAL Sum([EBITDA M]) , 0, 12))

Re: Rolling 12-Months sum

shamsu_mk — Mon, 11 Sep 2017 04:53:33 GMT

Hi Anil

EBITDA M is my monthly EBITDA. If I use your formula I get the same numbers as EBITDA M. I have calculated the Rolling EBITDA in excel which showing in the field EBITDA R. The result should match with the EBITDA R

Re: Rolling 12-Months sum

shamsu_mk — Mon, 11 Sep 2017 04:56:21 GMT

Dear Andrew

I did the arrangement in a way as you said. Please look in to my qvw file. Bt i did not understand that how to choose Accumulate 12 steps back.

Re: Rolling 12-Months sum

shamsu_mk — Mon, 11 Sep 2017 05:15:29 GMT

Dear Sunny

Your reply is some what matching with my requirement, but a small issue. I have already calculated the Rolling EBITDA in excel and uploaded for checking purpose. You can see the field EBITDA R is the rolling 12 months EBITDA. That means "R.Sum2" should match with "EBITDA R"

I have a problem to use the function "Rage Sum " because my report will show only one month data at a time, it will not be a monthly table. Since it is showing only one month data it will not be possible to use Rage Sum

Also, i noticed that in some month the rolling EBITDA is not correct as per the formula is given by you.

If I select June 17, the rolling EBITDA should be the sum of EBITDA M from Jul16 to June 17. This is what i need.

Re: Rolling 12-Months sum

sunny_talwar — Mon, 11 Sep 2017 11:26:10 GMT

Check this

RangeSum(Above(TOTAL Sum({<Year, Month>}[EBITDA M]) , 0, 12)) * Avg(1)

Re: Rolling 12-Months sum

shamsu_mk — Mon, 11 Sep 2017 12:08:12 GMT

Dear Sunny

Thank you very much. It is working perfectly. But I have a question. Did you suppress zero?

When I use this formula it is displaying all the years and months as zero and the selected month shows the value. Is there any other way without suppressing zero to display only the selected period?

Re: Rolling 12-Months sum

sunny_talwar — Mon, 11 Sep 2017 12:17:27 GMT

Yes, I did do suppress zero... is that not working?

Re: Rolling 12-Months sum

shamsu_mk — Mon, 11 Sep 2017 12:20:44 GMT

It is working. I expect some issue may happen if i link to my original report. This formula i am using for some bank covenants report. I will continue with this way and if I face any issue let us see later.

Once again thank you.

Also thanks to all members who are comment on my query.

Re: Rolling 12-Months sum

andrei_delta — Mon, 11 Sep 2017 12:47:40 GMT

Hello,

Look at this post: Set Analysis for Rolling Periods

Re: Rolling 12-Months sum

shamsu_mk — Tue, 19 Sep 2017 07:59:27 GMT

The following is the formula from my existing report.

sum({< [Exec P&L Heading] = {'EBITDA'}>} Amount)/1000000*-1

I want to replace EBITDA from the above formula with the below mentioned formula we have made. Please support.

RangeSum(Above(TOTAL Sum({<Year, Month>}[EBITDA M]) , 0, 12)) * Avg(1)

Re: Rolling 12-Months sum

sunny_talwar — Tue, 19 Sep 2017 11:21:31 GMT

Not sure I understand... do you want to elaborate?

Re: Rolling 12-Months sum

shamsu_mk — Wed, 20 Sep 2017 12:38:25 GMT

Sorry, I think I confused you.

The below is the expression we recently developed.

RangeSum(Above(TOTAL Sum({<Year, Month>}[EBITDA M]) , 0, 12)) * Avg(1)

We already had the data in the table for [EBITDA M].

in my new report, I need to calculate [EBITDA M]) and the formula is as follows.

sum({< [Exec P&L Heading] = {'EBITDA'}>} Amount)/1000000*-1

That means I want to replace with new formula as mentioned below.

RangeSum(Above(TOTAL Sum({<Year, Month>}[EBITDA M]) sum({< [Exec P&L Heading] = {'EBITDA'}>} Amount)/1000000*-1 , 0, 12)) * Avg(1)

Strikethrough has to be removed and replaced with the underline formula.

Re: Rolling 12-Months sum

sunny_talwar — Wed, 20 Sep 2017 12:50:38 GMT

May be this

RangeSum(Above(TOTAL Sum({< [Exec P&L Heading] = {'EBITDA'}, Year, Month>} Amount)/1000000*-1 , 0, 12)) * Avg(1)

Re: Rolling 12-Months sum

shamsu_mk — Mon, 25 Sep 2017 07:05:21 GMT

Thank you. It is working now. Still, i have a small issue and I am checking that. Suppress zero is not working.

Re: Rolling 12-Months sum

shamsu_mk — Mon, 02 Oct 2017 03:48:16 GMT

Hello

I still could not solve the issue to suppress zero. I selected only 2017 Jan to Sep. But it displaying all other years with zero value. I checked the "Suppress Zero Value" from Presentation tab option but still same. Please help me

Re: Rolling 12-Months sum

sunny_talwar — Mon, 02 Oct 2017 11:19:09 GMT

May have you use an if statement for your indicator column in the end

Re: Rolling 12-Months sum

shamsu_mk — Mon, 02 Oct 2017 12:57:07 GMT

Can you please give an example of if statement adding with the below formula ?

RangeSum(Above(TOTAL Sum({< [Exec P&L Heading] = {'EBITDA'}, Year, Month>} Amount)/1000000*-1 , 0, 12)) * Avg(1)