Transfer Key Figures on level PRODCUST back to level PERPRODCUST

[former_member287350]

Dear experts,

I tried for the first time to utilize a planning level without time period profile (PRODCUST in comparison to PERPRODCUST) to perform a calculation similar as explained in this older post ().

As explained in the post, I performed the following steps:

Assume PERPRODCUST is the base planning level and monthly time granularity

1. KF1@ PERPRODCUST = IF ((PERIODID - $$PERIODIDCU$$ <= 2) and (PERIODID -        $$PERIODIDCU$$ >=0) , CONSDEMANDQTY,null)

     Extract the values for Current Month, M+1 and M+2

2. Add additional calculation. This will sum up all the values for Current Month, M+1 and M+2

    KF1@PRODCUST = SUM(KF1@ PERPRODCUST)

3.

    a. Create a helper key figure HKF1@PRODCUST to calculate average

        HKF1@ PRODCUST  = (KF1@PRODCUST / 3)

    b. Create helper key figure and pass input key figure with time dimensions for e.g. KF   

        DMDPLANQTY

       This will put the average value in Current period.

       HKF1@PERPRODCUST = IF ((PERIOIDID=$$PERIODIDCU$$), HKF1@ PRODCUST, null)

    c. KF2@PERPRODCUST = HKF1@PERPRODCUST

I really like the concept of utilizing the planning level PRODCUST. However, in Step 3b where the Helper Key Figure created at level PRODCUST is transferred to planning level PERPRODCUST, I receive an error:

The planning level for the output key figure and the planning level for the input key figure must match in the calculation definition.

As the input key figure HKF1@PRODCUST does not have PER as required for the output key figure HKF1@PERPRODCUST, I understand why the error pops up. Is there a flaw in the calculation, or did I miss something? My general question would be how it is possible to transfer a value of a KF at a level PRODCUST back to KFs on level PERPRODCUST.

Best regards

Holger

[former_member217157 · Accepted solution]

You can add a calculation:

HNULL@PERPRODCUST = NULL (I/P: CONSDEMANDQTY@PERPRODCUST)

Change 3b to:

HKF1@PERPRODCUST = IF ((PERIOIDID=$$PERIODIDCU$$), HKF1@ PRODCUST, HNULL@PERPRODCUST)

Thanks,

Raghav

[former_member287350 · Accepted solution]

Hi Raghav,

do you mean I have to create an additional Helper Key Figure "HNULL"?

Thats what I tried out. I have never seen an expression like you suggested:

HNULL@PERPRODCUST = NULL (I/P: KF1@PERPRODCUST)

Could you please explain what the "NULL" and the "I/P:" are doing?

Also, I tried to implement your suggestion and received a syntax error:

Exception raised from  InternalError: dberror(PreparedStatement.execute): 2048 - column store error: fail to create scenario:  [34011] Inconsistent calculation model;CalculationNode (6_107) -> attributes -> calculatedAttribute (HNULL) -> expression:Expression is not valid: Evaluator: syntax error in expression string;expected TK_RPAREN,parsing 'fixed( NULL(I/P:  [here]"TESTHEIM"), 12, 6)',Details (Errors):

- CalculationNode (6_107) -> attributes -> calculatedAttribute (HNULL) -> expression: Expression is not valid: Evaluator: syntax error in expression string;expected TK_RPAREN,parsing 'fixed( NULL(I/P:  [here]"TESTHEIM"), 12, 6)'.

To be precise, my Key Figure Calculation for HNULL looks the follows:

HNULL@MONTHPRODCUST =  NULL(I/P:  "TESTHEIM@MONTHPRODCUST")

Did I do a syntax mistake here?

Thank you

Holger

[former_member217157 · Accepted solution]

Hi Holger,

The I/P means you add that additional KF to the Key Figure Input of the calculation.

eg HNULL@MONTHPRODCUST =  NULL. Then click inputs button and choose CONSDEMANDQTY@PERPRODCUST)

Hope this helps

Regards,

Raghav

[former_member287350]

Thank you for your help Raghav and Yeu. With Raghavs additional comment I was able to make it work exactly as I wanted it to be.

[Former Member]

Hi Eimla,

I´m facing a similar issue in my model, could you solve this issue at the end?

As I understand we cannot use the expression:

HKF1@PERPRODCUST = IF ((PERIOIDID=$$PERIODIDCU$$), HKF1@ PRODCUST, null)

In spite it was recommended to use the following one:

HKF1@PERPRODCUST = IF ((PERIOIDID=$$PERIODIDCU$$), HKF1@ PRODCUST,HNULL@PERPRODCUST)

I followed your steps and received the same error after addind this key figure calculation to the model:

HNULL@PERPRODCUST = NULL (I/P: KF1@PERPRODCUST)

Did you use the following one?

HNULL@MONTHPRODCUST =  NULL(I/P:  "TESTHEIM@MONTHPRODCUST")

Shouldn´t be the same as using the level PERPRODCUST?

Thanks and Regards

André

[Irmi_Kuntze]

Hey Andre

you should not re-open an old and already closed thread, but instead open a new referring to the old, next time please 🙂

However, general explanation is that all elements that are in the "left" side must be filled by the "right" side

Raghavs recommendation was to replace the assignement of a simple NULL in the step 3b to HKF1@PERPRODCUST by a helper HNULL@PERPRODCUST which is NULL as well.

The formula is simply HNULL@PERPRODCUST = NULL.

But by marking an additional key figure on the same level PERPRODCUST in the input parameters of the formula, the requirement to have information for left side in the right side is fullfilled and the KF can be activated

[yeushengteo]

Hello Holger

The step 2. will already runs into error since the planning level attributes are different, I believe you can only do assignment instead of a SUM.

Try KF1@PRODCUST = KF1@ PERPRODCUST.

Regards.

YS

[former_member287350]

Yes you are right, I noticed this and already changed it when I adapted the steps. Sadly correcting step 2 does not solve the issue i face at step 3b

[yeushengteo]

How about breaking it into 2 definitions:

HKF1@PRODCUST = IF ((PERIOIDID=$$PERIODIDCU$$), HKF1@ PRODCUST, null)

HKF1@PERPRODCUST = HKF1@PRODCUST

[former_member287350]

Unfortunately, calculation 1 will not work this way as there is no PERIODID to compare on level PRODCUST

[yeushengteo]

Oh.. missed out that part. Sorry

Can you calculate the avg at PERPRODCUST level via period transformation (-1 and -2 mth) and then reassign back to the PRODCUST level instead? Something like below thoughi am not sure if the IF condition is correct based on your requirement

KF1@PERPRODCUST = IF (PERIODID = $$PERIODIDCU$$), (( CONSDEMANDQT@PERPRODCUST +

                                      CONSDEMANDQT@PERPRODCUST2 +

                                      CONSDEMANDQT@PERPRODCUST3) / 3), NULL)

KF1@PRODCUST = KF1@PERPRODCUST