Calculation

[Former Member]

I am getting data from SQL server like below:

0calmonth 0amount

01.2000 100

02.2000 200

03.2000 -200

04.2000 300

......

01.2001 0

02.2001 100

03.2001 150

..

Now in Query designer I need to create output as below:

CalMonth 01.2000 02.2000 03.2000 04.2000 ....................................01.2001 02.2001 03.2001

Sum(0amount) 100 300 100 400 0 100 250

If this was just one column, I would have created this easily with the help of customer exit variable ( When I know the last Calmonth), but in this case i need to add this for all months. How I can handle this scenario?

Regards

Amandeep Sharma

Edited by: AmanSharma123 on Jun 10, 2011 7:20 AM

[Former Member · Accepted solution]

Hi,

My suggestion will be to do this calculation at infoprovider level.

In BEx, you have one option. Add Calmonth and Amount in the Column. Go to Key Figure properties -> Calculate Single Value as -> Cumulative along the Row.

But this is just a display property, it does not actually calculate. SO you cant use these values for further calculations. If suppose you filter out one month, value of that month will be ignored

[Former Member · Accepted solution]

Hi Parth,

Yes after changing the property, I got the out exactly what I want. I need to check whether user will filter it month wise.

Can you please further guide me what must be the proper way to calculate this on infoprovider level? how can we achieve that?

Thank you for your guidance.

Aman

[Former Member · Accepted solution]

Hi,

I am not aware of any automatic method. I would go for ABAP coding in tranformation.

In fact just yesterday a wrote a similar code in another thread

/message/10342555#10342555 [original link is broken]

Check and let me know if you need anything.

[Former Member · Accepted solution]

HI,

There is std code for such scenarios in the FI GL flow. Account balances are calculated in a similar way. The balance in this month is forwarded further. Check the note below for code.

1233941 - Start routines for key figure 0BALANCE (see Note 1153944)

You can build similar logic. The note consider fisc per whereas for you it is calmonth.

[Former Member · Accepted solution]

Hi Parth, Murali.

Let me explore the first option with users and see how it works.

Writing ABAP code will be very complex in my scenario as planning data is stored in different cube. Will think about this option if nothing works out for me.

I will keep you guys posted. Thank you for your guidance again.

Regards

Aman Sharma

[Former Member · Accepted solution]

Hi Murali,

Just a dumb question. Can we achieve the same at query level with the help if Virtual KF? I understand that Virtual KF will make query very slow. Just curious. If we can do that at query level, do you have any similar code for calculating YTD KF in virtual KF?

Please guide me.

Regards

Aman

[Former Member · Accepted solution]

Hi Parth,

Can you please give me to the link of the thread you gave me again. Somehow its not working now. I need to have a look at the code, seems like we need to implement ABAP code for this problem.

Regards

Aman

[Former Member]

Use 0calmonth in the Column. Place it above the keyfigure structure in the column.

This should solve your problem..

--- Thanks...