Current Year MTD, Prior Year MTD, Current Year YTD, Prior Year YTD as of a given Date

[mhathi]

Hello,

The requirement looks simple, perhaps is simple but I don't get it.

The Time Dimension and it's associated Hierarchy has been defined.

I have working logical fact columns using the AGO function for Prior Month, Prior Year Sales Dollars.

Then a requirement comes, that for Current Month sales, the user wants to compare the same exact time period for the Prior Month/Year.

So it's 23-MAR-2016 today so:

Current Year MTD = Sum of Sales $ from 01-MAR-2016 to 23-MAR-2016

Current Year YTD = Sum of Sales $ from 01-JAN-2016 to 23-MAR-2016

I defined the two new measures above using ToDate( Sales $, Month or Year). 

This works as long as I have a Date in the Analysis, either as a filter or as a Table prompt area of Analysis.

Prior Year YTD = Sum of Sales $ for 01-JAN-2015 to 23-MAR-2015

I defined Prior Year YTD as TODATE(AGO(Sales $, Year,1 ),  Year ) and this seems to work as well.

The problem is with the below measure:

Prior Year MTD = Sum of Sales $ for 01-MAR-2015 to 23-MAR-2015

Cannot seem to find a combination of AGO and TODATE to give me MAR-2015 from 1 to 23.

Any ideas?

Thanks,

Manish

[Sovitendu]

Hi Manish,
If you are displaying these reports on a dashboard then what you can do is create  various prompts to calculate/derive  the dates for a selected date, set presentation variables for those prompts.

Put all the prompts in a dashboard & hide the section so that they are not displayed.

Apply column level filter ( based on thr filter) to to the measures. For e.g filter(sales_measure between pv1 and pv2)

I think with this you can meet your requirements.

If you have any q then pls reply.

Regards,
Sovi

--
--
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to
obiee-enterprise-met...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

---
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-met...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[mhathi]

How would that solve my Prior Year MTD issue? No combination of Timeseries functions seems to work regardless of how you prompt it or just hard code for today's date in the filters.

[Sovitendu]

What you need to do is derieve two expressions which gives you two values I.e.

1.First day of the current month prior year of the input date
2. Current date last year of the input date.

Assign Presentation variables against these two expressions.

Apply filter to the measure value in the fx  based in these two PV.

Well I have followed the same approach to achieve similar reporting requirement for my project ofcouse with different date selections. Let me check if I have the similar code available then will share with you.

Regards,
Sovi

[mhathi]

Thanks Sovitendu.

[mhathi]

So after some Google-fu and suggestion here, I have come to the conclusion that the Time Series functions cannot be used reliably for a Prior Month/Year-to-Date comparison in Analyses. There is an old thread with exchange between a poster and Jeff MQ and Kurt Wolff (http://oracle.ittoolbox.com/groups/technical-functional/oracle-bi-l/ytd-last-year-ytd-help-needed-in-obiee-10g-4257365) that spells out some of the reasons. Although the post is for for 10g version, it seems no enhancements have been made since then.

So in order to get a true Month-to-Date for Prior year as well as Year to Date of prior year, I ended up using the FILTER function.

Would appreciate if anyone can point out any gotchas with this approach.

For Prior Year MTD:

FILTER("Fact"."Sales $ USD"  USING ("Sales Date"."Date" between TIMESTAMPADD(SQL_TSI_YEAR, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( @{P_DATE}{CURRENT_DATE}) * -(1) + 1, @{P_DATE}{CURRENT_DATE}) ) and TIMESTAMPADD( SQL_TSI_YEAR , -1, @{P_DATE}{CURRENT_DATE})))

For Prior Year YTD:

FILTER("Fact"."Sales $ USD"  USING ("Sales Date"."Date" between TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM @{P_DATE}{CURRENT_DATE}) * -(1) + 1, @{P_DATE}{CURRENT_DATE}))   and TIMESTAMPADD( SQL_TSI_YEAR , -1, @{P_DATE}{CURRENT_DATE})))

The Analysis Filter then becomes (1st of Prior Year to Current Date):

"Sales Date"."Date" BETWEEN TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM date '@{P_DATE}{2016-01-01}'

) * -(1) + 1, date '@{P_DATE}{2016-01-01}'))

AND date '@{P_DATE}{2016-03-25}'

Thanks,

Manish