Script logic question Allocation ans cumulated KPI

[former_member269849]

Hi Experts,

Business logic

I have a KPI wich is rate of production,

So I have  Daily production : loaded value and  Calendar wich is an input value

rate of production  = Cumulative value of production and / cumulative value of calendar.

so normaly the script should be executed after inputing value of calendar loading value of daily production ( DM , daily load )

both of cumulative value of production and cumulative value of calendar are ACCTYPE AST.

normaly the cumulative value should be repated  till the end of year .

Scipt :

daily calendar  of treatement :EXTR0020

cumulative value of calendar:EXT001757

Daily production :EXTR0094

Cumulative value of production:EXTR00940

rate of production:EXTR0021

so I have in my DEFAULT

//EXTR0020->input value

*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN TV_EXTRACTION

*IS EXTR0020

*REC(EXPRESSION=[MEASURES].[YTD],TV_EXTRACTION = EXT001757)

*ENDWHEN

*COMMIT

//EXT001757-> input value

*WHEN TV_EXTRACTION

*IS EXT001757

*REC(EXPRESSION=[TV_EXTRACTION].[EXTR00940]/[TV_EXTRACTION].[EXT001757],TV_EXTRACTION = EXTR0021)

*ENDWHEN

*COMMIT

////Allocation

*SELECT(%LASTTID%,[TIMEID],JOUR,ID=%JOUR_SET%)

*SELECT(%Y%,[YEAR],JOUR,ID=%JOUR_SET%)

*SELECT(%BS%,[ID],TV_EXTRACTION,ACCTYPE=AST)

*SELECT(%TARGET%,[ID],JOUR,TIMEID>%LASTTID% AND YEAR=%Y% AND CALC=N)

*XDIM_MEMBERSET TV_EXTRACTION=%BS%

*RUNALLOCATION

*FACTOR=1

*DIM JOUR WHAT=%JOUR_SET%;WHERE=%TARGET%

*ENDALLOCATION

*COMMIT

CUMUL.LGF

//Cmulatiive production

*XDIM_MEMBERSET JOUR=BAS(2016)

*XDIM_MEMBERSET TV_EXTRACTION=EXTR0094

*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN_REF_DATA = MASTER_DATA

*WHEN TV_EXTRACTION

*IS *

*REC(EXPRESSION=[MEASURES].[YTD],TV_EXTRACTION = EXTR00940)

*ENDWHEN

*COMMIT

//

//rate of production

*XDIM_MEMBERSET TV_EXTRACTION=EXTR00940

*XDIM_MEMBERSET JOUR=BAS(2016)

*WHEN_REF_DATA = MASTER_DATA

*WHEN TV_EXTRACTION

*IS *

*REC(EXPRESSION=%VALUE%/[TV_EXTRACTION].[EXT001757],TV_EXTRACTION = EXTR0021)

*ENDWHEN

*COMMIT

When I execute The Default.LGF and then CUMIL.LGF I have correct result

in the loaded value but not the input value.

and when I write an input value the cumulative value of the loaded value is not correct .

You find bellow the sceenshots.

thank you.

regards

[former_member269849]

Loaded values after executing package link when  execute  Default.LGF and then CUMUL.LGF

Input Value

[former_member186338 · Accepted solution]

Sorry, but it's absolutely incorrect idea to calculate rate KPI using script logic!

"rate of production  = Cumulative value of production and / cumulative value of calendar."

If you want to have rate type KPI - use dimension member formula.

Vadim

[former_member269849 · Accepted solution]

you are right but the problem is in cumulative value of the KPI.

after executing the package link we have the cumulative value of loaded value is correct and after inputing value we have the cumulative value of the input value correct  and the other no

[former_member186338]

Please read:

Don't divide in script logic!

[former_member269849]

If I delete the script of the calculation of rate and i put it like member formula , the result will still be incorretct because the  nominator or the Denominator are incorrect because the accumulated value is incorrect.

[former_member186338]

Please simplify the case - without division! Create member formula for EXTR0021.

Please provide ACCTYPE for the accounts used in script logic! What is the model storage type?

"When I execute The Default.LGF and then CUMIL.LGF I have correct result

in the loaded value but not the input value.

and when I write an input value the cumulative value of the loaded value is not correct ."

Please explain step by step what are you doing?

What do you mean by "input value"? What value?

[former_member186338]

P.S. I have already told you - NEVER use useless COMMIT! COMMIT is not saving data it just reset the scope of the script!

[former_member269849]

Hi Vadim ,

thank' s for reply, so I have :

daily calendar  of treatement :EXTR0020  -EXP

cumulative value of calendar:EXT001757 - AST

Daily production :EXTR0094 - EXP

Cumulative value of production:EXTR00940 AST

all cumulative values are AST wich is normal and the last value should be repeated till the last month to have value at year and .

I have to Types of KPI , one should be inputed  , other should be loaded , and fot both types we should calculate the cumulative value .

- the storage of the model is Periodic .

Steps

1- load value or inputed value( using save button)

-> load value the calculation of the cumulative value using Cumul.LGF

->Inputed value the calculation are in the DEfauTl.lgf

2-Allocation should be executed : repeating last value of the cumated KPI till las month of year.

regards

[former_member186338]

"I have to Types of KPI , one should be inputed  , other should be loaded ," - not clear - what is loaded and how, what is inputted?

[former_member269849]

inputed ( excuse my english by the way )but when we have input form and  we write a number and we click on the save button of the epm addin tool bar  .

I meant to say I have two types of KPI.

[former_member186338]

Which member is inputted?

[former_member269849]

EXTR0020 : daily calendar of treatement

[former_member186338]

And EXTR0094 is loaded?

How the load is organized?

[former_member269849]

yes loaded , I have daily load with no selection.

[former_member186338]

Do you select running default.lgf for loaded data?

Do you use package link?

[former_member269849]

Yes  I use pckage link where I have default.lgf and then cumul.LGF.

ues I select running.LGF for loaded data because I have other KPI that should be claculated  from loaded data .

[former_member186338]

Not clear! Why you use default.lgf in package link??? As a package?

Please show screenshot of package link setup...

[former_member186338]

I am unable to understand your logic, you have to provide all details of your packages including data load package!

[former_member269849]

I have shceduled the packege linf to run every day , and in this pckages link ( the default.LGF run first and then the cumul.LGF run after,

for the DM load I have /CPMB/LOAD_INFOPROV_UI with no selection. standard advanced script

and for the cumul.LGF I Have /CPMB/DEFAULT_FORMULAS

advanced script : stadard

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,CUMUL.LGF)

[former_member186338]

Sorry, but I am unable to ask hundreds questions!

FULL INFO with screenshots!

"for the DM load I have /CPMB/LOAD_INFOPROV_UI with no selection. standard advanced script" - what about default.lgf setting for this chain???

[former_member269849]

I'm sorry Vadim , I know i'm bothering a lot .

for the default.LGF setting I have no settings .

It's the solution you suggested to me  to do in another SCN question , the problem here that you did not know that I have imputed values because if not all will be correct.

[former_member186338]

Ok, let me try to explain the most common scenario:

Each day the data can be loaded for EXTR0094 for some days within one year

Each day the data can be saved using input template for EXTR0020 for some days within one year.

You want to have YTD results of EXTR0094 and EXTR0020 in EXT001757 and EXTR00940 in all days of the current year.

The rest can be calculated using dimension member formulas.

Is it correct?

[former_member269849]

Yes Exaactly

[former_member186338]

Then calculate both in default.lgf:

*XDIM_MEMBERSET JOUR=BAS(2016)

*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN_REF_DATA = MASTER_DATA

*WHEN TV_EXTRACTION

*IS EXTR0094

*REC(EXPRESSION=[MEASURES].[YTD],TV_EXTRACTION = EXTR00940)

*IS EXTR0020

*REC(EXPRESSION=[MEASURES].[YTD],TV_EXTRACTION = EXT001757)

*ENDWHEN

And in /CPMB/LOAD_INFOPROV_UI set the parameter to run default.lgf. No package link!

P.S. This script will recalculate everything for the whole year for this 2 accounts. If you need better performance - then badi.

[former_member269849]

Still have the same problem .

after executing the  default . LGF

[former_member269849]

and here is it after wrinting the inputed value

Correct resutls

[former_member186338]

How do you execute it???

[former_member186338]

You have to launch DM package with the chain /CPMB/LOAD_INFOPROV_UI

And with

TASK(/CPMB/LOAD_IP,RUNLOGIC,1)

If there is some data for EXTR0094 to load then full year will be recalculated

[former_member269849]

I execute it manyally .

[former_member186338]

Absolutely incorrect idea! Incorrect scope will be used.

You have to execute it as a part of /CPMB/LOAD_INFOPROV_UI chain

After data upload the scope will contain member EXTR0094 and the script will run for this member.

[former_member269849]

I meant I execute it with the chain f /CPMB/LOAD_INFOPROV_UI with selecting the run logic option , sorry for missundestanding of the question.

[former_member269849]

this is what i did exactly

[former_member269849]

I've added the task of run logic still wrong values.

[former_member186338]

Then it's a question - do you actually load some data for EXTR0094?

And in the test report please show the row with YTD measure for EXTR0094

[former_member269849]

Yes i'm loading ddata for EXTR0094

here is the screenshot you asked for , sorry for being late i'm actually working with vpn.

[former_member186338]

I do not understand what you are doing wrong. The script in default.lgf has to calculate the uploaded data the same way as for input form. May be you have added something to default.lgf?

[former_member269849]

in my default.LGF i have nothing that can affect the resullt.

[former_member269849]

it's woking now thank you Vadim , please I read you document of average value many times but did not understad it , could you please  simplify it for me what should I do exactly in this case, to understand it .

thank you so much .

regards.

[former_member186338]

Easy:

If you need to show some ratio KPI - don't use script logic to calculate ratio!

Use dimension member formula to perform division.

Vadim

[former_member269849]

but when you use member formula an you have  a=b/c and dimension formula we will have in total SUM(a)/SUM(b) but if we use  script I shoud have in total SUM(a/b) .

what I need to calculate is  SUM(a/b) not SUM(a)/SUM(b).

thank you .

[former_member186338]

Not clear what do you mean....

SUM(a/b) -???

[former_member186338]

It's like sum of percentages - meaningless...

[former_member269849]

I mean what I will have displayed at the total of KPI ratio.

at the total of a i want to have SUM of (b/c) not  SUM(b)/SUM(c).

[former_member269849]

at the total of a total year dor example:

member formula   SUM(b)/SUM(c)

scrpit logic           SUM(b/c)

[former_member269849]

but that's what the client want to dipkay in some Ratios. that why I put the division on scrpit.

[former_member186338]

Please explain in details the business value of a/b

For year total in AST account you will have DEC a/DEC b

[former_member186338]

"Please explain in details the business value of a/b"

[former_member186338]

Sorry, I have to ask 3rd time - what is the business value?????????

Sometimes the client do not understand what he want

[former_member186338]

Please look on the sample calculation with 2 AST members used in dimension member formula (including totals!):

[former_member269849]

Thank you so much Vadim four your kind help and time .

[former_member269849]

For AST Member it's Okay because we have the division of last values ,  for the client some time I have KPI Ratio when I need to do calculation with member formula  like percentages but at total for EXP ACCTYPES  where I have :

The Nominator and the denominator are   EXP members . here, the member formula is calculating using SUM of nomilator / sum of denominator  , for some KPI it's useful for other KPI it's not useful i don't know how to explain the business logic because their calculation are very complicated and when i did this by formula , they said that they are not the correct values , that's why i did it with script and here  the  execution is not very optimized.