Evaluate pivot formula expressions loaded via inline

[hugmarcel]

Hi

having this data:

SALES:

LOAD * INLINE [

KPI,  SALESGROUP,  SALESMAN,   AMOUNT, KPI-FORMULA

KPI1, GROUP1,  SALESMAN1,  10,  SUM(AMOUNT + 1)

KPI2, GROUP1,   SALESMAN1,  30,  SUM(AMOUNT + 1)

KPI3, GROUP1,   SALESMAN1,  40,  SUM(AMOUNT + 1)

KPI4, GROUP2,   SALESMAN2,  20,  SUM(AMOUNT + 1)

];

When I display the data in a pivot and use field "=KPI-FORMULA" as a formula, "SUM(AMOUNT + 1)" will be displayed.

But I want that expression "SUM(AMOUNT + 1)" is calculated and displayed as 11, 21, 31, 41 in the Pivot.

How to get this? I could not find something line =eval(KPI-FORMULA) as a digramm function.

Thx - Marcel

[Anonymous]

Hi,

Please check the below code for above requirement.

SALES:

LOAD * INLINE [

KPI,  SALESGROUP,  SALESMAN,   AMOUNT, KPI-FORMULA

KPI1, GROUP1,  SALESMAN1,  10,  SUM(AMOUNT + 1)

KPI2, GROUP1,   SALESMAN1,  30,  SUM(AMOUNT + 1)

KPI3, GROUP1,   SALESMAN1,  40,  SUM(AMOUNT + 1)

KPI4, GROUP2,   SALESMAN2,  20,  SUM(AMOUNT + 1)

];
TEST:
load
*,
IF(AMOUNT<>0,AMOUNT+1)AS KPI_FORMULA
Resident SALES;

DROP TABLE SALES;

[Anonymous · Accepted solution]

You can use dollar sign expansion. See section 24.4 of the latest reference manual:

24.4 Dollar-Sign Expansions

Dollar-sign expansions are definitions of text replacements used in the script or in expressions. This process is known as expansion - even if the new text is shorter. The replacement is made just before the script statement or the expression is evaluated. Technically it is a macro expansion. A macro expansion always begins with '$(' and ends with ') ' and the content between brackets defines how the text replacement will be done. To avoid confusion with script macros we will henceforth refer to macro expansions as dollar-sign expansions.

See attached example. I removed the - signed from the KPI-FORMULA in case this had a negative effect.

Jonathan

[kumarnatarajan]

Hi,

Try this.

SALES:

LOAD *,RangeSum(AMOUNT,1) as [KPI-FORMULA] INLINE [

KPI,  SALESGROUP,  SALESMAN,   AMOUNT

KPI1, GROUP1,  SALESMAN1,  10 

KPI2, GROUP1,   SALESMAN1,  30

KPI3, GROUP1,   SALESMAN1,  40

KPI4, GROUP2,   SALESMAN2,  20

];

[jonathandienst]

Hi

If $(KPI-FORMULA) evaluates to SUM(AMOUNT+1), use a second dollar expansion like this:

$($(FORMULA)) or $(=$(FORMULA))

This will return the result of Sum(AMOUNT+1)

Hope that helps

Jonathan

[hugmarcel]

Great & thx a lot for all the solutions!

I knew about the scripting and "translate to $variable" solutions, but the dollar sign expansion serves me best!

Marcel

[#7]

Hello,

I have same type of trouble.

i'd like to calculate formula results into the loading script.

Indeed, with the same exemple from Marcel Hug, i want to perform an evaluate directly inside the Load script to be able to check results validity according to further criteria.

But i'm unable to do that.

here is my current script :

Result_Table:

LOAD

     SALESGROUP,

     evaluate(KPI-FORMULA) as ResultValue;

LOAD

     SALESGROUP,

     KPI-FORMULA,

     AMOUNT

RESIDENT CheckTable_Build;

But Evaluate don't give me any result.

Thanks a lot in advance for your help.