Using Characteristic in restricted Key figure

[Former Member]

Hi,

I need to show a column in the report in Bex, which show the net sales value(KF) for all those store, whose "store open date" is less then "the first day of current month in last year" -- (store open date<= "the first day of current month in last year")*net sales value.

"store open date" is one of the attribute of characteristic "sold_to", and I have created a varible base on 0calday to get the "the first day of current minth in last year".

but I still not sure how can I get the column i need in the report,could anyone giev me some advice?

Thank you,

Ping

[Former Member · Accepted solution]

Hi Ping,

Here is step-by-step solution.

- Put into columns your net sales KF.

- right click on KF structure – new formula.

- in the left frame put cursor on a ‘Formula variable’ node – new variable (for "store open date") – say, with ZOPEND name.

- give name and description, ‘Processing by’ = replacement path; ‘Characteristic’ = sold_to; ‘Replace variable with’ = Attribute value; ‘Attribute’ = your "store open date"; check ‘To Value’.

- in the left frame put cursor on a ‘Formula variable’ node – new variable (for "the first day of current month in last year") – say, with ZCUTDAY name.

- give name and description, ‘Processing by’ = customer exit; mandatory, not ready for input ‘Dimension ID’ = Date.

Into customer exit code place this:

DATA: DATE_CUT LIKE SY-DATUM,
  year TYPE i.
…

WHEN ‘ZCUTDAY’.
              CLEAR L_S_RANGE.
              DATE_CUT = sy-datum.
              DATE_CUT+6(2) = '01'.
              year = DATE_ CUT(4).
              year = year - 1.
              DATE_CUT(4) = year.

              L_S_RANGE-LOW = DATE_CUT.
              L_S_RANGE-SIGN = 'I'.
              L_S_RANGE-OPT = 'EQ'.
              APPEND L_S_RANGE TO E_T_RANGE.

- right click on KF structure – new formula: KF * (ZOPEND < ZCUTDAY).

- Hide first, KF column

- Create a condition with last formula value > 0.

Best regards,

Eugene

[Former Member · Accepted solution]

Eugene,

Your advice always help me out:)

only one thing i want to clarify with you about creating the variable ZOPEND: what is the difference between this two method below?

method1: ‘Characteristic’ = sold_to; ‘Replace variable with’ = Attribute value; ‘Attribute’ = your "store open date"; check ‘To Value’.

method2: ‘Characteristic’ = "store open date"; ‘Replace variable with’ = Key; 'Deminsion ID'= Date

as I trying to using the method1, but i can't find "store open date" under the attribute list. so in the end, i used method2 instead.

so could you please explain to me teh difference and also what might be teh reason i cana 't find "store open date" under the attribute list in method1.

Thank you once again,

Ping

[Former Member · Accepted solution]

Hi Ping,

If your attribute is a 'Display only' one, then you shouldn't see it in BEx.

But I see that you see it in method2. Do you have it as a nav attribute or a separate char in the infoprovider?

Best regards,

Eugene

[Former Member · Accepted solution]

sorry for my late reply. I was out of office these days.

the 'store open date' is the display attribute of the characteristic "ZRTL_Site", which has "0sold-to" as its nav attribute.

I have this "ZRTL_Site" as one of the infoprovide for the transactional ODS. and the query is just based on the infoset created on the transactional ODS.

am i explaining clearly, pls let me know.

Thank you,

Ping

[Former Member · Accepted solution]

Hi Ping,

The IO 0sold-to should have a tech name ZRTL_Site__0sold-to. And make sure thar in your infoprovider 0sold-to is also marked as a nav attribute to ZRTL_Site.

Best regards,

Eugene

[Former Member]

Hi,

Make store open date as navigational attribute and create a customer exit var on store open date to get the first day of current month in last year.

Now make a restricted key figure .

drag

net sales value and store open date .

restrict the store open date with the var u have created suppose ur var is zstordat then storeopen date restricted to zstordat<=first day of current month in last year.

This should solve ur problem.

Thanks

G.V.Ranganath

[Former Member]

Hi Ping,

Try the following.

Create a VAR variable of replacement path for Store open date. See the “How to calculate with attributes”:

http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sapportals.km.docs/documents/a1-8-4/how to calculate with attributes?state=7

Say, your var for "the first day of current minth in last year" has V2 name.

In the KF structure in columns add a new formula like

(VAR <= V2)* KF + (VAR > V2)* KF

Create a condition for showing only records with this formula > 0.

Best regards,

Eugene

[Former Member]

'Store Open Date' should be a navigational attribute of 'Sold to'. Right click -> Edit -> KF 'sales values' -> drag 'Store Open Date' to right side -> Right click 'Store open date' -> restrict -> variables(less than equal to) -> Variable on 'Store Open Date'.

Your variable should be on 'Store Open date', you can populate its value as "the first day of current minth in last year" .

assign points if helpful

raj

[Former Member]

How can I make the variabel based on "store open date"? How can I populate its value as "the first day of current minth in last year"?

as teh variable I created under 0calday seems not show up in teh variables under "store open date"?

[Former Member]

Ping,

Pls follow Eugene's explanation. The variable u created under 0calday will not show under any other characteristic. A variable is dependent on a characteristic.

You might have to write a customer exit to populate ur variable under 'store open date'

http://help.sap.com/saphelp_erp2004/helpdata/en/f1/0a56f5e09411d2acb90000e829fbfe/content.htm

raj

[Former Member]

Thank you for the reply.

I have created two formula variable, one for store open date(replacement path) - V1, another one is 'first day of current month/last year'(user exit)- V2

and I created a new formula like you suggested:

(V1<=V2)KY+(V1>V2)KY

but it seems not what I want. what I want the fomula done is only show the KY when V1<=V2, otherwise(if v1>v2) then will not show anything(eg: blank in that column).

I tried to change teh formular like :

(V1<=V2)KY+(V1>V2)0

but it doesn't work!

pls advice how i can get what i want.

Thank you,

Ping