Rolling Quarter Issue

[#1]

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,

[sunny_talwar · Accepted solution]

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)

-

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)

[#3]

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?

[sunny_talwar]

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

[#5]

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

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

[sunny_talwar]

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.

[#7]

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

[sunny_talwar]

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

[#9]

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