How to Caluculate the current year revenue and previous year revenue

Question

Hi,

Can anyone help me with the below criteria:

For a product I have to show the revenue for current year(i.e. 01-01-2016 to 31-08-2016) and for previous year(i.e. 01-01-2015 to 31-08-2015 with out using the dashboard prompts.

Your help is highly appreciated.

Thanks

Mithun

3061821 · Answer

Hi All,

Thank you for your responses.

i will check it out and get back to you.

I will check whether it is fetching the revenue for the current year from 01-01-2016 to 31-08-2016 and previous year from 01-01-2015 to 31-08-2015.

Thanks

Mithun

Gianni Ceresa · Answer

You can also simple add a filter to your analysis with your "year" column between YEAR(CURRENT_DATE)-1 AND YEAR(CURRENT_DATE) and use a pivot view ....

Actually there are many ways as that's the way OBIEE works: if you have a correct model with a proper time dimension by default your figures are aggregated at the year level. So no need of complication function (giving you a really bad performance).

Shams Abbasi · Answer

Try putting these sql in your edit formula and update if you are getting proper results

Previous Year

FILTER("Revenue Column Here" USING ("DATE Column" Between  TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE) -364,CURRENT_Date) and TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE),CURRENT_Date) ))

Current Year

FILTER("Revenue Column Here" USING ("Date Column" BETWEEN  TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE) +1,CURRENT_Date) and TIMESTAMPADD(SQL_TSI_DAY, -DAYOFYEAR(CURRENT_DATE) +366,CURRENT_Date) ))

Although i am not sure what will happen for leap year