Char in a Column

[Former Member]

Hi all;

I have a report where I have KF like amount1, amount2, amount3 and total_amount. But I have a requirement that I need to show a comumn with a 'latest_year'. For example amount1 for 2003 and amount2 is 2004. If the Project has no data after 2003, it's latest_year would be 2003. and I need to have the 2003 in latest_year column with that project.

Does anyone has this situation..kindly give me how to solve this.

thank you.

[Former Member]

Hi Krishma,

Here is almost step-by-step solution.

Suppose that you need to show results for 3 years (current, current-1,current-2).

- Create a variable on 0CALYEAR or 0FISCYEAR (user entry, mandatory, ready for input).

- Create a structure in columns.

- New selection (put into selection your KF and 0CALYEAR/0FISCYEAR). Let’s name it curr. Restrict it by the newly created variable.

- CTRL-C + CTRL-V to copy curr. Edit new selection – name it curr-1. Enter offset = - 1 for a variable.

- CTRL-C + CTRL-V to copy curr-1. Edit new selection – name it curr-2. Enter offset = - 2 for a variable.

- New formula. Name it DIF. Into formula window enter

( 'cur' > 0 ) + NOT ( 'cur' > 0 ) * ( ( 'cur - 1' > 0 ) * 2 + NOT ( 'cur - 1' > 0 ) * ( 'cur - 2' > 0 ) * 3 )

Hide it.

- New formula. In the left frame find ‘Formula variable’ node. Right click on it – New variable. Make it customer exit, mandatory, not ready for input, Dimension ID = Number. In the code of this var determine return value from your UserEntryVarForCurrentYear. (To check the solution you can create user entry variable and customer exit var later).

- After creation of formula variable in the ‘Edit formula’ screen into the formula window insert the code:

( 'DIF > 0 ) * ( 'UserEntryVarForCurrentYear' - 'DIF' + 1 )

The last column will show the last year where KF is not zero. If all three years have zeros, the last year will be zero also.

For column names you can create text variables.

The only drawback of this solution is a usage of triada delimiters (if you use it, the last year will be shown like 2.005 or 2,005).

Best regards,

Eugene

[edwin_harpino]

hi,

you may try to create restricted keyfigure amount1, right click 'keyfigures' -> new selection, drag keyfigure amount2, and variable year with offset -1.

hope this helps.

[Former Member]

Hi;

Thanks for the reply..the problem is the year for that column needs to be populted from '0fiscyear', and it's not the amount I need to display ..I need to display the year. so I am not sure the offsets concepts would work. could you please explain me !

thanks.

[edwin_harpino]

hi,

i see...

you may try use text variable from 0fiscyear

http://help.sap.com/saphelp_nw04s/helpdata/en/85/e0c73cccbdd45be10000000a114084/frameset.htm

[Former Member]

Hi ;

Here is my senarios:

Proj Amt1(2002)Amt2(2003)Amt3(2004)TotalAmt Latest_Year

-

---

0012012 | 1000 | 2000 | -

---

| 3000 | 2003

0012013 | 1500 | -

---

| 200 | 1700 | 2004

-

---

How do I get the last column!!

Thanks.

[former_member188975]

Hi Krishma,

Do Amt1, 2 and 3 always correspond to 2002, 2003 and 2004? In that case you could do a simple formula to check something like if Amt3 = 0 then 2003. Or if Amt2 = 0 and Amt3 > 0 then 2004...is this what you are looking for or did I misunderstand?

[Former Member]

Hi Bhanu;

This almost what I am looking for but I need to display the value of Char 0fiscyear (example: 2002, 2003, 2004, or 2005 whichever has the value for this project (last activity), your explanation seems could work but how do I display that year instead of amount in my column. I was trying to create a new selection with char 0fiscyear but I can't create them without KF. I don't need any KF in this column. could you please explain

thanks.

[former_member188975]

Hi Krishma,

You can create a formula like: (Amt3<0)* 2003. But this approach is hardcoding the year values...not sure if this is what would be best in your situation...

[edwin_harpino]

perhaps use formula variable with replacement path for the fiscyear ?

http://help.sap.com/saphelp_nw04s/helpdata/en/ca/5f9ac61a205a459d0e7ef313d10321/frameset.htm

[Former Member]

I may have problem in case let's say the project don't have value on 2002 but it has values on 2003 and 2005. In this case the new column should have '2005' because it's the latest year. I can't compare with 0 because I have values on 2003 and 2005 both.

Also where do I create this formula anyways?

thanks.

[former_member188975]

To make it credible you would need to put in detailed If and Then conditions in the formula. You can start creating the formula in the key figure structure in your query. Right click and select New Formula.

Hope this helps...

[Former Member]

Bhanu;

thanks but I don't see any IF statement in my boolean while creating formula selection. I can't type also ..could you please elaborate? thanks.

[former_member188975]

Hi Krishma,

You would need to formulate the If Then using boolean operators and (). Sorry to confuse you but you cannot type in the words IF and THEN in the formula. Take a look at this link for more info:

http://help.sap.com/saphelp_nw04/helpdata/en/23/17f13a2f160f28e10000000a114084/content.htm

In your case it would be something like: (Amt1 < 0)2003. Double click the key figure to get it in the formula bar. This reads as If Amt1 is less than 0, the result of the expression in the brackets is 1 (true), so it leads to 12003 which is 2003. If Amt1 is more than 0, the result of the expression in the brackets is 0 (false), so it leads to 0*2003 which is 0.

Hope this helps...