Urgent: Formular question: get first/last month value with qty value

[Former Member]

We've got a query result as the following:

Jan_2007 -- Feb_2007 -- Mar_2007 -- Apr_2007

0 --- 54 --- 0 --- 3

23 ---0 --- 12 --- 7

In the above query result,

1st row shows the sales quantity in Jan_2007 is 0, in Feb_2007 is 54, in Mar_2007 is 0, and in Apr_2007 is 3.

2nd row shows the sales quantity in Jan_2007 is 23, in Feb_2007 is 0, in Mar_2007 is 12, and in Apr_2007 is 7.

We would like to add a new column to get the first/last month value with quantity, e.g., in 1st row, the 1st month value with quantity value (>0) is Feb_2007, and the last month value with quantity value (>0) is Apr_2007. Therefore the 1st month value with qty is Feb_2007 and the last month value with qty is Apr_2007. In 2nd row, the first month value with qty is Jan_2007 and the last month value with qty is Apr_2007. But how to use formular to get the 1st/last month values with qty?

We will give you reward points!

[ChandranGanesan · Accepted solution]

Hello Kevin,

You can create forumula using [Boolean Operator|http://help.sap.com/saphelp_nw04/helpdata/en/23/17f13a2f160f28e10000000a114084/content.htm]

IF<Logic Expression> THEN <Expression1> ELSE <Expression2> can also be made using a formula in the form

You can also use the [AND, OR Logical operators |http://help.sap.com/saphelp_nw04/helpdata/en/23/17f13a2f160f28e10000000a114084/content.htm]to check all the keyfigure columns.

Thanks Chandran

[Former Member · Accepted solution]

Chandran,

We know the boolen stuff. The problem is not here. We've tried different approaches like formula variable for the characteristic Calendar Year/Month and then use Aggregation Exception to get the MAX, MIN, but not working!

Thanks anyway!

[SanjivM · Accepted solution]

Kevin,

Would it be feasible for you to have RKFs for the number of months that you want to show the results for? If yes then you could have a CKF with boolean operator for the first RKF with non-zero qty and hence the month. Like wise for the last month.

Regards,

Sanjiv

[Former Member · Accepted solution]

hi Sanjiv,

I created a RKF with the 0CALMONTH to restrict the KF Quantity, but somehow when we run the report, found there is only one column other than many columns like Jan_1999, Feb_1999, ..., Apr_2011, May_2011. Any idea?

Thanks

[SanjivM · Accepted solution]

Hi Kevin,

Do you need to display for values for all months across all years? That may not be possible in using RKFs as it would need one RKF per period. That was the reason why I asked if it were feasible for you to create RKFs for your situation. It can be used for something like a rolling 12 months or similar situations but not from begining and ending of time.

Sorry that did not help!!

Regards,

Sanjiv