topic Re: Rolling Quarter Issue in QlikView

Rolling Quarter Issue

Tue, 01 Mar 2016 16:30:18 GMT

Hi All,

Please tell me if the below formula is the right formula for the rolling quarter. I want to be able to click on MonthYear field (Book Rev) from Jan 2016 to Nov 2015 subtracted by the results (Book Rev) from Oct 2015 to Aug 2015 divided by Book Cost from Oct 2015 to Aug 2015.

=(Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -2))) & '<=' & Date(MonthEnd(Max(accountingdate))))"}>} grossamount)

Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -5))) & '<=' & Date(MonthEnd(AddMonths(Max(accountingdate), -3))))"}>} grossamount) * 4)

Sum({[BookCost]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -5))) & '<=' & Date(MonthEnd(AddMonths(Max(accountingdate), -3))))"}>} grossamount)

When I manually calculated the formula came out to around 1.15 but this formula comes to -2.747.

Thanks,

Re: Rolling Quarter Issue

sunny_talwar — Tue, 01 Mar 2016 16:46:51 GMT

Not sure what how your manual calculation is going, but can you check which one of these don't match:

(8818559.46 - (66101177.01 * 4))/6414342.83

the only thing I you can change is:

(8818559.46 - 66101177.01) * 4/6414342.83 which gives 1.377

=(Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -2))) & '<=' & Date(MonthEnd(Max(accountingdate))))"}>} grossamount)

Re: Rolling Quarter Issue

Tue, 01 Mar 2016 16:57:34 GMT

Thanks Sunny for your awesome answer. One last piece of the pie I have, if possible and out of courtesy can you please briefly explain me the formula?

Re: Rolling Quarter Issue

sunny_talwar — Tue, 01 Mar 2016 17:00:26 GMT

Is there anything specific you don't understand. I mean there are a lot of things going on in the formula, it would be easier to explain parts then to go over the whole thing in detail

Re: Rolling Quarter Issue

Tue, 01 Mar 2016 17:05:14 GMT

=(Sum({[BookRev]<NumMonth, Month, [DimYear], accountingdate = {"$(='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -2))) & '<=' & Date(MonthEnd(Max(accountingdate))))"}>} grossamount)

Can we explain the negative numbers -3,-5,-2, etc.... please?

Re: Rolling Quarter Issue

sunny_talwar — Tue, 01 Mar 2016 17:13:01 GMT

Sure. The best way to understand the negative number is to create text box object and add each of the below to see what they give

1) ='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -2))) & '<=' & Date(MonthEnd(Max(accountingdate)))

2) ='>=' & Date(MonthStart(AddMonths(Max(accountingdate), -5))) & '<=' & Date(MonthEnd(AddMonths(Max(accountingdate), -3)))

Based on your selection these will give you different range of data. Assuming you selected Jan-2016,

1st one will give you >=11/01/2015 <= 01/31/2016

2nd one will give you >=08/01/2016 <= 10/31/2015

Now these are being use in the set analysis of your expression to restrict the sums to those date ranges to get you the output you were looking for.

Re: Rolling Quarter Issue

Tue, 01 Mar 2016 17:23:53 GMT

It makes things so much easier...now the monthend, monthstart and addmonths please.

Re: Rolling Quarter Issue

sunny_talwar — Tue, 01 Mar 2016 17:32:01 GMT

Does are date and time function just used to manipulate the date range you want to look at. You can get more help on them here: Date and time functions ‒ QlikView

Re: Rolling Quarter Issue

Tue, 01 Mar 2016 17:34:51 GMT

Thanks for all your help Sunny!!!!!!