Calculated keyfigure AR Balance Greater than Zero

[Former Member]

Hello

I need some inputs for the requirement below-

If the AR Balance is Negative than display zero; hence I create a keyfigure like below

(AR>0)*AR. This works fine if I have the rows drilldowned.

My requirement is is it possible to have the values calculated even if I do not drilldown and have the report @ a aggregated level.??

As currently as soon as I remove the drilldown (rows) the calculated keyfigure does not perform the above calculation or you can say it just considers the currently displayed value for calculation. Hence losing out on my requirement.

Let me know in case you need more clarification.

Example - I need to view the value as 70 but as soon as I remove the Bill-to from drilldown the calculations are not how I want it to be.... is it someway I can hold on to 70 value even if i remove drilldonw???

Company Code Billto AR Balance > 0 AR Balance

A BB1 0 -10

A BB2 20 20

B BB3 20 20

C BB4 30 30

-

---

Result 70 60

After I remove the drilldown "billto"

Company Code AR Balance > 0 AR Balance

A 10 10

B 20 20

C 30 30

-

---

Result 60 60

Amit

[Former Member · Accepted solution]

Hi AL,

For the main CKF -

Kept the Previous exception aggregation as total &reference characterstic as bill-to. Also, selected calculate result as summation

Created New Formula -

Inserted the CKF in it and selected exception aggregation as total &reference characterstic as comp code.

It didnt help... still the same....

I also tried by

For the main CKF -

changed the Previous exception aggregation as use "standard Aggr" and performed the above steps.. but no help yet...

Any other tricks?

-Amit

[anshu_lilhori · Accepted solution]

Another thing you need to do is make a formula variable with replacement path for comp code in replacement path tab choose replace variable with infoobject-replace with attribute value-attribute constant 1..in currency/unit ttab choose number..

Create another fromula variable for bill to following with the above steps.

Now multiply your c keyfigure with this formual variable.

Hope it helps.

Regards,

AL

[Former Member · Accepted solution]

Hello AL,

The solution provided works perfectly... thanks AL

I have one question though.

I am trying to use the Hierarchy [Row(Area)] i.e. the characteristics will be displayed in hierarchy format in ROW.

The issue is when the hierarchy is collapsed no values are displayed but as soon as I expand the hierarchy the values are displayed correctly. The CKF calculates and displays all the values when expanded..

Do you have any idea whether the solution provided by you works with hierarchy too?

Regards,

Amit

[Former Member]

Hi AL,

I created only one replacement variable and it worked ..i.e. for Bill to and multiplied it with the main CKF.

Kept the exception aggr settings as mentioned by you before for Total & bill to ...

and it seems to be working... let me try out any other scenarios just in case if it suffice all of them...

as of now... Like it

Thanks for your help buddy

full points

Amit

[Former Member]

Yes AL,

I did the exception aggregation as mentioned by you but it didnt work as expected.

-amit

[anshu_lilhori]

ok lets give another try

use nested exception aggregation:

make new formula and put your ckf into it angain excerption aggregation as total but this time reference characterstic as

comp code.

Another think to check---the ckf which you have created for that select calculate result as summation and see that works or not.

regards,

AL

[Former Member]

Hi AL

It didnt help It is still the same....

I tried to check the "calculation After Aggregation" tick too.... but didnt help..

any other way?

-Amit

[anshu_lilhori]

Hi,

Did you try exception aggregation or not?

[anshu_lilhori]

Hi,

Please try out condition like this:

(AR < 0 ) * 0 + (AR > 0)* AR

Also in case if this doesnt work then Put a exception aggregation on your ckf..Aggregation as total and reference characterstic as Bill to.

Please get back to me after giving a try to it.

hope it helps.

Regards,

AL