KF Calculation if one of 2 inputs are not initialized

[marc_laberge]

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):

[Irmi_Kuntze · Accepted solution]

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

[marc_laberge · Accepted solution]

Hallelujah !

Point #2 solved the issue finally !

This means JJ was right as well, I just didn't do it correctly the first time.

Thanks all.

[Former Member · Accepted solution]

Great news! Glad it worked for you.

[marc_laberge]

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.

[Alecsandra]

Hi Marc,

There is something I don't understand: aren't both W29 and W30 part of the same month?

If yes, why don't we see KAMLIFTCENTSOFF values available in week 29? Given that is available in week 30 and it has same split rule from month to week, I would expect to see same value in 29 as in 30.

What do I miss?

Regards

Alecsandra

[marc_laberge]

Hi,

All of this is done at week level.

The Cents Off is the rebate entered for a Promo Week in the system.

I'm not sure why we need to check this at the month level.

Thanks,

Marc

[Alecsandra]

My misunderstanding, sorry. I didn't read carefully the role of the split factor.

It might be that initialization was not successful. Could you please do a quick test to see if this is the case? Add the supposed missing combination directly from excel and see if it works afterwards.

[marc_laberge]

Thanks for all the help by the way !

No changes. Still the same thing.

I'm really getting annoyed by this !

[marc_laberge]

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.

[Former Member]

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.

[marc_laberge]

Hi,

Thanks for the suggestion, but It does not seem to change anything to the behavior.

What I'm getting from you suggestion is that a Helper KF would ne required to be initialized normally?

[Alecsandra]

Hi Marc,

ISNULL check will not work unless the planning combination is already initialized.

ISNULL will return 1 when value is available and 0 when there in no value but the time series is already created for the planning combination.

In order to create the missing time periods you could use the parameter CREATE_TIMEPERIODS of the Copy Operator.

Regards

Alecsandra

[marc_laberge]

That is what I thought.

We're trying to avoid having to use a job just to initialize the planning combinaison.

What we do currently is use a Helper KF that is simply equals to 1 and use one of our forecasted KF as an input. It pretty much works. However this time, the planning levels are different and I don't forecast anything at that planning level.

So I guess I'm stuck with using a Copy Operator.

[Former Member]

I guess i did forget to mention the important part of the evaluation. You need to include both kfs as inputs in each check evaluation even though you are only using the values of one kf in each individual evaluation.

In reading your calc:

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

I interpret the @promo and @promo1 to be equivalent planning levels otherwise you couldn't perform a subtraction operation.

My suggestion is to put the kfs through an intermediate check.

checkA@perprodpospromo = if(isnull(kamliftcentsoff@perprodpospromo),0,kamliftcentsoff@perprodpospromo)

Inputs to "checkA" would be both kamliftcentsoff@perprodpospromo and kamliftcentsoff1@perprodpospromo1

Then evaluate the other KF

checkB@perprodpospromo1 = if(isnull(kamliftcentsoff1@perprodpospromo1),0,kamliftcentsof1f@perprodpospromo1)

Inputs to "checkB" would be both kamliftcentsoff@perprodpospromo and kamliftcentsoff1@perprodpospromo1

Now do your comparison:

IF(  "checkA@PERPRODPOSPROMO"  -  "checkB@PERPRODPOSPROMO1"  > 0 ,  "checkA@PERPRODPOSPROMO"  ,  "checkB@PERPRODPOSPROMO1"  )

[marc_laberge]

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.