Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hello!
You can find in attachement a simple instance of finding difference between quarters over the year.
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)
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
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
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
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.
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)
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