topic QoQ caluclation in QlikView

QoQ caluclation

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

Hi all,

how to caluclate the Quarter on Quarter ?

what it exactly meant? and how we will do this? can anyone have sample please help me to understand.

Thanks

Sony.

Re: QoQ caluclation

Sergey_Shuklin — Tue, 21 Aug 2018 10:29:46 GMT

Hello!

You can find in attachement a simple instance of finding difference between quarters over the year.

Re: QoQ caluclation

BalaBhaskar_Qlik — Tue, 21 Aug 2018 14:43:30 GMT

May be like this:

1. All days of the quarter are selected. =Year(Date)&'_Q'&ceil(month(Date)/3)

2. Define a variable vYQ-1 based on the current date selection, calculated one quarter back (minus 92 days) and used Year Function: =Year(date(max(Date)-92,'DD.MM.YYYY'))

3. Define a variable vQ-1 based on the current date selection, calculated one quarter back (minus 92 days) and used the function to get: =ceil(month(date(max(Date)-92,'DD.MM.YYYY'))/3)

4. Use Set Analysis in the chart to get the "Previous Quarter" from the current selection =Sum({1<Year={$(vYQ-1)},_Qtr={$(vQ-1)} >} Amount)

Re: QoQ caluclation

soniasweety — Thu, 30 Aug 2018 04:00:21 GMT

hi balabhaskarqlik

Thanks for response. but what exactly i need is

QoQ and YoY

i have a Quarter field till current quarter and i have TCV(amount field) so how can i create expressions for Q/Q and Y/Y?

2017Q1

2017Q2...........2018Q4

Re: QoQ caluclation

dx_anupam — Thu, 30 Aug 2018 07:00:21 GMT

Hi Sony

Quarter on quarter (QOQ) is a measuring technique that calculates the change between one financial quarter and the previous financial quarter. This is similar to the year-over-year (YOY) measure, which compares the quarter of one year (such as the first quarter of 2018) to the same quarter of the previous year (the first quarter of 2017). The measure gives investors and analysts an idea of how a company is growing over each quarter.

To achieve this you can add a column with formula Quartername([Date Field]) as “ Quarter B_date ” Quarter name of orderdate. Create a variable at report lavel which will calculate quarter name of current quarter and quarter of previous year using below formula

Currentquarter = Quartername(Today())

Previousquarter = Quartername(Today(),-4)

Then use these calculations in below formula

= (Sum({<[Quarter B_date]={"$(= Currentquarter)"}>}Sales) ) /(Sum({<[Quarter B_date]={"$(= Previousquarter)"}>}Sales) )

It will return you growth QOQ

Regards,

Anupam

Re: QoQ caluclation

soniasweety — Thu, 30 Aug 2018 07:22:14 GMT

thanks dx.anupam . i have couple of Doubts

1. i have quarter field is derived from DB - FIscal Period values like 2017Q1,2017Q2......so on

SO here instead of date how can i use Quarter directly?

= (Sum({<[Quarter B_date]={"$(= Currentquarter)"}>}Sales) ) /(Sum({<[Quarter B_date]={"$(= Previousquarter)"}>}Sales) )

2. if user selects 2018Q3 the above expression works dynamically? means it will caluclate the 2018Q3 and 2017Q3?

3. How abot YOY how can i derive those variables and expressions?

Thanks

Re: QoQ caluclation

dx_anupam — Thu, 30 Aug 2018 08:45:45 GMT

i have quarter field is derived from DB - FIscal Period values like 2017Q1,2017Q2......so on

I am not aware of any ootb function which will convert date field in format you have in db.if you have to convert it you have to use 'Q' & ceil(month(UCCDATE)/3) which will be difficult to compare in set analysis.

SO here instead of date how can i use Quarter directly?

= (Sum({<[Quarter B_date]={"$(= Currentquarter)"}>}Sales) ) /(Sum({<[Quarter B_date]={"$(= Previousquarter)"}>}Sales) )

I don’t think it’s possible to use directly in above formula. If you use it will return result as 0.

2. if user selects 2018Q3 the above expression works dynamically? means it will caluclate the 2018Q3 and 2017Q3?

No provided formula is not compatible with user selection. To make it compatible you will need to store the respective date in a variable and use it instead of today() in below formula

Currentquarter = Quartername(Today())

Previousquarter = Quartername(Today(),-4)

How abot YOY how can i derive those variables and expressions?

For YoY its quite simple

Follow the same process above

To achieve this you can add a column with formula Year([Date Field]) as “ Year B_date ” Year of orderdate. Create a variable at report lavel which will calculate quarter name of current quarter and quarter of previous year using below formula

CurrentYear = Year(Today())

Previousyear = (Quartername(Today())-4)

Then use these calculations in below formula

= (Sum({<[Year B_date]={"$(= CurrentYear)"}>}Sales) ) /(Sum({<[Year B_date]={"$(= Previousyear)"}>}Sales) )

It will return you growth YOY

Regards,

Anupam