IF ELSE statement / CMOD Code in BEX Query

[Former Member]

Hi

I have a requirement in Inventory flow.

Material stock value at the end of each quarter has to be calculator based on cycle count indicator. I have all the values on my cube.

In the report, Calender year and quarter is on the selection screen.

When user enters 2014 and Q2, Data for April, May and June are displaying on the report. But user want data only for June.

At the end of quarter 2 is June, so he wants only Material stock value at the end of June.

If a material stock level is changed in April and May and not changed in June, not data is available for June in my Cube.

In this case for Quarter 2, if June data is missing, it has to look for May data. Even if MAY data is missing, it will have to look for April.

Even if April data is missing, it will have to look for March data.

The result should be

Plant    Q1    Q2    Q3   Q4

XYZ     100    100  100    100

If there is no change to stock level in April, May & June - at the end of Quarter 2, the stock value should be 100 as it was in end of Q1.

If no change was dont to stock level until December, the stock level should be 100.

Is there a way out using IF ELSE statements or through CMOD customer exit.

Regards,

Elango Murgesan

[Former Member]

hi,

its a simple solution, create a customer exit on 0calmonth. from the user input for the quarter (Q2) calculate the month (i.e. June). Create a RKF where the data is restricted to the calyear and the calmonth <= June. this way even if the data in june is not there it would capture the previous amount and show it in report.

regards,

Arvind.

[ccc_ccc]

Hi Elango,

I gone through the your requirement, I understand few questions and my comments in below.

When user enters 2014 and Q2, Data for April, May and June are displaying on the report. But user want data only for June.

1 Remove Quarter Infoobject from report

2 Crete formula variable with text

3 Ask Enter only Q1, Q2, Q3 and Q4.

4 Create customer exit for 0CALMONTH infoobject and hide it

5 Through CMOD populate only June , when Q1.

At the end of quarter 2 is June, so he wants only Material stock value at the end of June.

if end of Q2 (check in CMOD with current date) and populate value June into 0CALMONTH variable

Remaining two question, i am not clear, if more information, then I will try to give some light.

Regards,

Nanda

[Former Member]

Hi Nanda,

I have info objects 0CALQUARTER, 0CALQUART1, 0CALMONTH2, 0CALMONTH & 0CALYEAR.

Using 0CALMONTH2 in filter i can restrict to allow only months Mar, June, Sep & December. Thats works fine.

But, If a Material XYZ, the stock quantity for Jan = 100, Feb = 150 and March = No change to stock quantity. For Business people - At the end of Q1, the stock quantity for material XYZ = 150.

In MARDH table, changes will be captured only if there is change to Material quantity. So there will be no entry for March. I will load that to DSO and CUBE. In Cube, there will be entries for JAN and FEB.

In the report when User looks for 2014, Q1 - The query should look for March, if not entry available in March, it should look for Feb, still no entry it should look for JAN and bring quantity.

Regards,

Elango Murugesan

[Former Member]

Hi Elango,

Can we do this?

Create 3 KFs, restricted with calmonth with customer exit variable each.

if user enters Q1, pass the months to each customer exit variable. Jan,feb,mar.

in the formula, you can make a condition, if mar KF>0(i,.e KF restricted with march month), then show march kf. if mar kf==0, then feb KF and so on.

i hope this woudl work.

Pleae chk and let us know if this satifies your requirements.

Regards,
Sakthi.

[Former Member]

Hi Sakthi,

My requirement is

                                 Quantity

Plant  Material   JAN    FEB    MAR

X        A               100    50     (No entry in Cube since no changes in the material quantity)

X        B               50     60       100

Now my inventory at end of Q1 for plant X and material A = 50 (Feb value since it is the last known value of Q1)

        my inventory at end of Q1 for plant X and material B = 100 (March value since it is the last know value of Q1)

When is look at Plant level, inventory at end of Q1 for plant X = 150. I need this result.

I dont need Material on the result output. With Material on rows, i get data. I want without material like this below. Hide material does not work since

Plant  Q1

X        150

Regards,

Elango Murugesan

[Former Member]

Hi Elango,

I think that should work..

As I said, in a formula, have these conditions(like if Mar>0,then mar KF and so on). so in teh end , it will have one single value on this.

so in your above example,

Plant  Material  QuanKF(derived based on formula)

X          A           50

X           B          100

on removing material ,it should definitely aggregates according to Plant.

[Former Member]

Hi Sakthi,

I achieved that through Nested exceptional aggregation. Created CKF1 on quantity with Aggregation Last Value on 0CALMONTH. Created CKF 2 on CKF1 with Summation on Material. This gave me the exact result without material on the rows.

Now i have additional requirements on the same report.

Plant Q1 Total   Q2              Total

A       50   50    (no entries)   50

At the end of Q1 total = 50, no entries in CUBE for Q2, then the result at the end of Q2 should be 50. Values of Q1 should be carried forward for Q2.

But when i select Q2 against the materail, no data displays since no entries available in Q2. But i need Q2 total as 50.

Is this possible to achieve using IF else or CMOD ?

Regards,

Elango Murugesan

[Former Member]

HI Elango,

the user input on CALQuarter always would be Single value??or can we expect ranges as well?

if single value,

then we can achieve it by having one more dummy KF.

lets say. user input is Q2. 

-> in your dummy KF, restrict the Calquarter/Calmonth to last quarter(i.e Q1)using customer exit and make the logic as you said above(last value based on calmonth).(make this KF hidden, as this is not required to display)

-> and in another KF restricted with Q2.Same logic, last value on Calmonth.((make this KF hidden, as this is not required to display)

and now you have 2 KFs in your report.  So in a formula, you can have condition-> if Q2 KF is blank, then Q1KF, else Q2 KF.

Hope you are getting my idea.. Please try this and let us know.

** if Q1 also null, then we can extend customer exit logic to may be last one year, and having last value on based on calendar month, we can still achieve i guess.

Regards,
Sakthi.

[Former Member]

Hi Elango,

If in case you are trying the above option...wanted to mention one more point.

The user input for calquarter, you need to remove from Filters section. instead, create a user input formula variable and use the same in Q2 KF.

Regards,

Sakthi.