Disaggregation Expression in null or zero values

[former_member75927]

Hello I'm trying to do a disagg the values of Key figure in null and zero values of another key figure.

Per example. I have a Key figure calculated that is the average of price of family of products. When I run the disagg opperator the average values, overwrite all the values of KF of price, and its should substitute only values nulls and zero.

In my kf of price I configured a Disaggregation expression with the following formula:

IF(ISNULL("FCSTPRICE") OR "FCSTPRICE" = 0, "FCSTPRICE", NULL)

My source KF have a planning level different of the target KF,

What could be wrong in my configuration?

Is there something that I forgotten?

The configuration of the formula in disagg opperator in only in target KF? Or in source? Or both?

Regards

[Irmi_Kuntze · Accepted solution]

you cannot filter his way. Even if you would be able to filter on KF values, the result would filter out certain planning combination. But it would NOT filter on period 3, 7, 8 and 10 for example

So you HAVE to calculate the final result in a separate KF an cop from there ALL vales to your target. If the operator finds that a value is already existing the same way, it will not re-write the same value again. But of it recognizes that the value is slightly different, it woud overwrite

Therefore the source KF of the copy operator must only set the value of the AVG in those periods where the target KF has no value

How to: I described that in my previous answer already

[Irmi_Kuntze]

ok, let's assume as follow:

Source, calculated:

KFSOURCE@WKFAMILYCURR =

IF(ISNULL("FCSTPRICE@TargetPL") OR "FCSTPRICE@TargetPL" = 0,

"FCSTPRICE@TargetPL", NULL)

Target: Stored, no formula, FCSTPRICE@TargetPL, which may not have a value in every period, so you want to replace the missing FCSTPRICE with the average of the existing periods of the same key figure? And you want to use the advance copy operator (instead of the old DISAGG operator as it is better), not an on-the-fly calculation?

And I assume that targetPL can fully build the source planning level of WKFAMILYCURR ?

I dont see where you provide the average of the non-null-values.

But you need more KF for that then just the one, because the source KF must already have ALL periods fill with non-nulls. The advanced copy operator cannot copy only specific periods, it copies all periods.

So what you need to do is to calculate the average in the KF FCSTPRICE on the desired aggregated level from which you want to pik the average (assumingly WKFAMILYCURR) by an AVG( FCSTPRICE@WKFAMILYCURR )

Then you WOULD need a calculation in your FCSTPRICE@BPL (BPL = base planning level on which you store the price) which is like

"FCSTPRICE@BPL" = IF(ISNULL("FCSTPRICE@BPL") OR "FCSTPRICE@BPL" = 0,

"FCSTPRICE@WKFAMILYCURR", NULL)

As that is not possible (you cannot have the same KF on 2 different levels as input), you need a helper in-between that does

HFCSTPRICE@WKFAMILYCURR = FCSTPRICE@WKFAMILYCURR

And then your formula would change to

"FCSTPRICE@BPL" = IF(ISNULL("FCSTPRICE@BPL") OR "FCSTPRICE@BPL" = 0,

"HFCSTPRICE@WKFAMILYCURR", NULL)

Now you have an on-the-fly calculation, and if you want that values stored, you use FCSTPRICE as both source and target in the copy operator and copy on level BPL

Should work - IF I understand your requirement correct

You really would help yourself if you provide a proper description best with proper KF names and proper planning level names in the first place 🙂

Irmi

[former_member75927]

The calculation of the average of the KF of price is already working as well.

What is not working yet is the replace the values null and zeros in the target KF.

As you told, I can not do a dissag expression using a calculated KF, then, I created a KF stored to receive a values of average price throught a normal disagg (without expression), and then, make a filtered copy opperator to null and zero values (Is it possible?) what there is already working well is, the KF of price, the KF calculating the average of price, and I have a kf auxiliar of price, as you meaned, a helper. What I want to know now is how can I transfer the values of the KF of average to null and zero values in the target. I already did the disagg from the average values in PL WKFAMILYCURR to a KF auxiliar in my base planning level, So, right now I need to know if is it possible to do a advanced copy opperator filtering the values of target KF

Regards

[Irmi_Kuntze]

Your description is a bit fuzzy to me. Your target is to replace the 0 and NULL values always with NULL always? Or do you want to replace 0 values with the values from the source KF?

What is the source and what the target of the DISAGG / ADVANCE COPY OPERATOR, a stored KF, is that the FCSTPRICE? Cannot be a calculated KF obviously.

And can you please list all source and target KF with their planning level?

[former_member75927]

Hello, my target is replace the null and 0 values with the average value calculated. The source is a KF calculated, that is the average values, and the target is the KF of price.

Souce PL: WKFAMILYCURR

Target PL: WK-PROD-SALESREG-INCOTERM-LOCATION-CHANNEL-CUSTOMER-SEGMENTATION

My souce KF is calculated, in this case, there is another tool that i could use to do it?

I thought in disagg the values in an auxiliar KF (Stored), and then do a copy opperator between the price KF and AUX.

Regards