topic Re: QoQ Calculation help required in QlikView

QoQ Calculation help required

Thu, 05 Feb 2015 12:23:49 GMT

I am trying to achieve Quarter on Quarter calculation; I don’t have a date field, as such the granularity is year and quarter. I need to calculate following:

Q1FY12/Q1YF11 as Q1FY12

Q2FY12/Q2YF11 as Q2FY12

Q3FY12/Q3YF11 as Q3FY12

Q4FY12/Q4YF11 as Q4FY12

FY12/FY11 as FY12 (Full Year View)

Q1FY13/Q1YF12 as Q1FY13

Q2FY13/Q1YF12 as Q2FY13…and so on.

Its a pivot table with dimensions Quarter and Year with the following expression, I am trying to use the following formula, Before is working fine with 1 dimension FQY (Fiscal Quarter) as soon as ‘m adding full year (Fiscal Year) to get the sales for total year its not giving me any results per se.

Need your help to find a solution here…please refer the attached QV for more details.

(Sum(SalesUSD)/1000000)

/
Before((Sum(SalesUSD)/1000000),4)-1

Thanks!

Nidhish Khare

Nidhish.khare@gmail.com

Re: QoQ Calculation help required

rubenmarin — Thu, 05 Feb 2015 12:42:46 GMT

Hi Nidhish, you can use the TOTAL qualifier:

(Sum(SalesUSD)/1000000)

Before(TOTAL (Sum(SalesUSD)/1000000),4)-1

Re: QoQ Calculation help required

Thu, 05 Feb 2015 12:53:30 GMT

Thanks Ruben!

Added the TOTAL, FQY number are coming fine now, however, 'm still not getting the total of Fiscal Year. !

QVW attached in the original message, thanks for the help!

Re: QoQ Calculation help required

rubenmarin — Thu, 05 Feb 2015 13:57:48 GMT

Hi Nidhish, I didn't get this working on the same table, hope other user gives you the solution.

Another aproach will be concatenate the sames data using [Fiscal Year]+1 as [Fiscal Year] and SalesUSD as SalesUSDLY (adding where clause to load only data till 05/02/2014)

This way for each fiscal year you have the values from the last year and you can use an expression like:

If(SecondaryDimensionality()=1, (Sum(SalesUSD)/1000000)/(Sum(SalesUSDLY)/1000000),

(Sum(SalesUSD)/1000000)/Before(TOTAL(Sum(SalesUSD)/1000000),4)-1)