Current Month Function

building reports webintelligence
1

Hello Everyone,

In our reports we created in report filters that automatically return current month data, a maintenance free filter. However there is a little problem to this approach.
For example here is our function:

=If FormatDate(CurrentDate();"yyyy-MM")=FormatDate([Create Date];"yyyy-MM") Then "Y" Else "N"

This function works great except for one day a month, where a new month starts, the first day. Our data is always at least the previous days data. So for example today is 5/1 so when you open the report it’s blank no data to retrieve for current month. I need help writing a function like that will always show current month data based on the last date availble in the universe.

So for example today is 5/1 but I need the report to recognize the last nearest date was 4/30 and hence still realizes it’s the last month.

We use SAP OLAP universes generated off of BW queries. So no universe manipulation.

Any help will be appreciated!

Thanks,

Rasesh :hb:

roshpatel :us: (BOB member since 2009-02-04)

2

Try this,

=If FormatDate(CurrentDate();"yyyy-MM")=FormatDate(([Create Date]-1);"yyyy-MM") Then "Y" Else "N"

KhoushikTTT :us: (BOB member since 2005-02-24)

3

Thanks I’m sorry I did not specify this.

The values we are using this for are not always in MM/DD/YYYY.
The equation that I’m using only gives us date in the format as MM/YYYY.
So how would the function need to be to address that format?

Thanks I have applied your other function to the universes that provide dates in the MM/DD/YYYY.

Thanks,
Rasesh

roshpatel :us: (BOB member since 2009-02-04)

4

The report level solution would be a dirty huge formula with multiple nested If() statements. Go ahead with the universe solution.

KhoushikTTT :us: (BOB member since 2005-02-24)