Variance of key figure in same column as an adjocent column in BEx query

[nikhil_joy2]

Hello All,

I have a requirement to show the variance of particular key figure as an adjacent column in a query. I am using this query in SAP Design Studio and will enable exceptions in the variance column.

PFB example. I would like to have 12 month data like this with the variance from previous month as next column. (for Feb, 105-100=5)

I can't fix each month value in a key figure structure since its not useful for me in SAP Design studio. I think, with fixed KF structure, its possible by cell reference. Any possibility to achieve this by defining a formula with exception aggregation as variance? I get values only in total when I try that.

calmonth	count	variance
Jan	100
Feb	105	5
Mar	98	-7
Apr	103	5
May	89	-14

Please suggest some solution. Its urgent and quick response would be highly appreciated.

thanks in advance

Regards,

Nikhil Joy

[Former Member]

Hi Nikhil,

Give a try by using exception aggregation : variance function with Ref Char as calmonth on KF count .Hope this will work for you.

Regards,

sunil.

[nikhil_joy2]

Hello Sunil,

Thanks for the reply. I tried this already. My KF is non-cumulative and when I try exceptional aggregation, the new formula KF in which I applied exceptional aggregation as variance is showing all zeros. But showing some value in result set.

Do you have any idea why  I am getting zeros in all rows? PFB screenshot of output.

Also, As Ricardo has mentioned I tried to create variable and apply offset. Unfortunately, the TIME characteristic is "CHAR" datatype so that offset is not possible with variable on TIME. I cant change data model now.

The source infoprovider is  transient provider generated from BPC cube and NOT modelled in BW side. That could be the reason why TIME characteristic in CHAR datatype I guess. I am stuck up in this stage and hoping to go for a customer exit variable to calculate previous month value.

Do you think any other option possible?

Regards,

Nikhil Joy

[Former Member]

You can also work your model adding to the Infocube a 2nd key figure for "Previous period".

Always when you load data, you load information in your original KF.

in addition, is necesary to add a new rule group to add the same value in the 2nd KF but with your Period+1 (you are creating the future row).

[Former Member]

Hi Nikhil,

For the non-cumulative Kf's exception aggregation functions like AV1,AV2,First Value,Last Value,Max and Min will work so that might be the reason your getting zeros.Look for the option for going user exit or virtual KF and convert the char to time.

[Former Member]

Hi Nikhil,

Mybe you can try this:

Create a replacement path variable (ZVAR) referenced to your time InfoObject (like 0CALMONTH or 0CALMONTH2, etc).

Create a fix structure in columns and créate two key figures.

In the first KF add a restriction using your recent created variable (ZVAR) and applies a "Constant selection"

In the second KF add the restriction using the same variable minus 1 (ZVAR - 1) and applies a "Constant selection".

Maybe this can Works.

Regards!

[nikhil_joy2]

Hi Ricardo,

Thanks a lot for your reply.

I will try this out and update you the result.

Regards,

Nikhil Joy