Skip to Content

KF Calculation if one of 2 inputs are not initialized

Hi,

I have a calculation that that selects the higher value between 2 KF.

The calculation works only if both KF are initialized, I would like for it to work even if I have only a value in one KF.

Is this possible ?

Do you have tricks to initialize a KF without have to actually load data in it ?

Here is my calculation:

IF( "KAMLIFTCENTSOFF@PERPRODPOSPROMO" - "KAMLIFTCENTSOFFW1@PERPRODPOSPROMO1" > 0 , "KAMLIFTCENTSOFF@PERPRODPOSPROMO" , "KAMLIFTCENTSOFFW1@PERPRODPOSPROMO1" )

Here is the Screen of the Planning View (I want the red areas to be calculated):

http://image.prntscr.com/image/2f0ba5164bea4cb29ecf13ac5576c25d.png

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 04, 2016 at 12:11 PM

    Hi Marc

    I think you need to distinguish two things here:

    1: If you split by split factor HINIT@PERPRODPOSPROMO you must make sure that this KF is initialized in all relevant periods and all relevant planning objects.

    So the question is: how do you make sure that it has always value 1 in all periods/planning objects??? How did you check this requirement is fullfilled?

    As a test (I assume it is a helper and not a displayable KF), copy the HINIT into a calculated KF (on lowest level by KF@PERPRODPOSPROMO = HINIT@PERPRODPOSPROMO) and check in Excel by just loading the one KF and one week on the lowest level if really all planning objects show up. If that fails, this is your root cause

    2: I would calculate slightly different:

    Invent new calculated KF e.g.

    "KAMLIFTCENTSOFFW1B@PERPRODPOSPROMO" = "KAMLIFTCENTSOFFW1@PERPRODPOSPROMO1"

    With that you make sure you work on the same planning level and calculate

    MAX("KAMLIFTCENTSOFF@PERPRODPOSPROMO" , "KAMLIFTCENTSOFFW1B@PERPRODPOSPROMO" )

    With that it will always take whatever is initialized and of higher value

    Yours, Irmi

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 01, 2016 at 10:13 PM

    Put an if(isnull) statement against both key figures in an intermediate helper KF and if null make the value 0 else the underlying KF value. Then do your max value comparison against those two new helper kfs.

    Add comment
    10|10000 characters needed characters exceeded

    • This is pretty much what I was doing. Except for the part that you put bith KF as inputs.

      That's an interesting twist.

      My 2 planning Levels PERPRODPOSPROMO & PERPRODPOSPROMO1 are the same level except one is at Week-1.

      This means that the value in KAMLIFTCENTSOFF in Week15 is copied to Week14 to KAMLIFTCENTSOFFW1.

      Then I'm trying to figure if I already had a KAMLIFTCENTSOFF in Week14 and I take the MAX value for week14 between KAMLIFTCENTSOFF & KAMLIFTCENTSOFFW1.

      The issue is if one is not initialized the calculation fails.

  • Apr 03, 2016 at 06:55 PM

    Well,

    I have tried all the proposed solutions in this thread and I still cannot make it work properly.

    I have 3 KF in this calculation pattern.

    1. KAMLIFTCENTSOFF is entered at a aggregated level (Product and Store Family : PERMPGBANREGPROMO).

    • The Rebate value is then copied by multiplication of a Helper KF that is equal to 1 at our lowest planning level.
    • KAMLIFTCENTSOFF@PERPRODPOSPROMO = "KAMLIFTCENTSOFF@PERMPGBANREGPROMO" * "HINIT@PERPRODPOSPROMO"

    2. KAMLIFTCENTSOFFW1 is simply a 1 week offset of the first KF.

    3. CONSENSUSDEMLIFTCENTSOFF then tries to find the bigger value from the first 2 KF in one period.

    • CONSENSUSDEMLIFTCENTSOFF@PERPRODPOSPROMO = IF( "KAMLIFTCENTSOFF@PERPRODPOSPROMO" - "KAMLIFTCENTSOFFW1@PERPRODPOSPROMO1" > 0 , "KAMLIFTCENTSOFF@PERPRODPOSPROMO" , "KAMLIFTCENTSOFFW1@PERPRODPOSPROMO1" )

    I have tried the Helper KF and also to run the CREATE_TIMEPERIODS copy operator on all 3 KF. I still get a result only when KAMLIFTCENTSOFF has a value.

    http://i.imgur.com/gpYoC6V.png

    Add comment
    10|10000 characters needed characters exceeded

    • It seems that because the planning level used goes through the offset of the attribute transformation, the period is not initialized (or the value of KAMLIFTCENTSOFFW1 is considired in Week 30) without value in KAMLIFCENTSOFF.