QoQ caluclation

[soniasweety]

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.

[Sergey_Shuklin]

Hello!

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

[balabhaskarqlik]

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)

[soniasweety]

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 

[dx_anupam]

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

[soniasweety]

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

[dx_anupam]

1. 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.

1. 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)

1. 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