Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to calculate annualized revenue i.e. (sum of revenue for previous 4 quarters with respect to my selection)
Fiscal Year | Quarters | Revenue |
FY17 | Q1 | 42 |
FY17 | Q2 | 26 |
FY17 | Q3 | 89 |
FY17 | Q4 | 34 |
FY18 | Q1 | 32 |
FY18 | Q2 | 53 |
FY18 | Q3 | 75 |
FY18 | Q4 | 40 |
For example if I select FY18 Q3 the expression should return me the summed up revenue of FY18 Q3,Q2,Q1 and FY17 Q4 i.e. 194.
Also if there is no fiscal year selection by default it should consider FY18 .
Hi,
here i think you should create a quarter_year field to simplify the expressions on the front-end, otherwise it will be a bit complicated..
Hi Youssef,
Can you please share a detailed approach..
Thanks in advance
on the front end, to write expressions that roll back on the years months quarters etc, we use SET ANALYSIS using DATE field or Month_year field etc (you don't have such informations)..
here the idea is to create a numeric field from your fiscal_year and quarter fields, like this:
Autonumber(quarters&fiscal_year) as quarter_year_num
and use set analysis like this:
=sum({<quarter_year_num={">=$(=Max(quarter_year_num)-4 <=$(Max(quarter_year_num))"}>}Revenue)
tell me if you need a QVF if you couldn't make it work
Which object are you trying to do this in? What is the dimensions if this is in a chart object?