Dynamic Week needed - NO prompt

[Former Member]

Hi Gurus,

I would like to populate data to my dashboard using a Webi report. My datasource is BW query.

I would like to show the orders in my report if "invoice-week" <= current week

So, I need a dynamic week that I think needs to be passed onto "invoice-week" object in Webi report.

I do not want user to be prompted to enter the Week.

Where should I created the condition/formula (in Universe or Webi report)? to display the invoice records where 'Invoiice week" is <= current week

Please let me know the logic.

I think I need to include a condition in Universe :

<FILTER KEY="[ZP_OPRWK]"><CONDITION OPERATORCONDITION="<="><CONSTANT TECH_NAME="Value"></CONSTANT>[<CONSTANT></CONSTANT>]</CONDITION></FILTER>

Thank you,

Dev

[Former Member · Accepted solution]

Hi,

If you are using SAP we recommend to cxreate an SAP Exit or create a WebI Formula.

If you are using MSAS you can also define a universe pre-definedf filter in MDX such like this.

Here is a sample based on AdwentureWorks. This sample returns 1 if the current member from Date.Calendar is in the current week:

<FILTER EXPRESSION="IIF ( CDate([Date].[Calendar].CurrentMember.MemberValue) <= Now() and CDate([Date].[Calendar].CurrentMember.MemberValue) > Now()-7,1,0)">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT CAPTION="1"/>

</CONDITION>

</FILTER>

[Former Member · Accepted solution]

HI Didier,

I am using SAP.

Since, we are planning to build our dashboard on top of QAAWS, I think, I won't be able to use Webi Formula that will be created in Webi Report.

Let me know if SAP Exit is my best option.

-

Dev

[Former Member · Accepted solution]

How to create a Webi Formula for ww.yyyy (i.e., w = week, y = year)

[Former Member · Accepted solution]

For instance you can try this on the current date:

=FormatDate(CurrentDate(); "ww.yyyy")

For August 11, 2009, the result will be: 33.2009

[Former Member · Accepted solution]

Hi Didier,

How can I get last calendar week (i.e.., 32.2009 instead) ?

[Former Member · Accepted solution]

HI Dev,

If i'm not wrong in the Bo function list you can find the last week, last day,........... and try to use them for your requirement

Cheers,

Suresh Aluri.

[Former Member · Accepted solution]

Hi,

You can try this formula, I have tested it and it returns "32.2009":

=FormatDate(RelativeDate(CurrentDate(); -7); "ww.yyyy")

Didier

[Former Member · Accepted solution]

Suresh,

I checked but found only lastdayofmonth, lastdayofweek functions.

Didier's formula worked. Thank you very much!!!

Points assigned