Multiply (or divide) two values from the same dimension in load script

[#1]

I want to create calculated value based on other values from the same dimension. As an example, I have an account Sales and an account COGS. Now I want to create a new account "COGS in % of Sales". This would be easy in a table but I need to create this in the load script.

LoadData:

LOAD * INLINE [

    Period, Account, Value

    201201, Sales, 100

    201201, COGS, 50

    201202, Sales, 110

    201202, COGS, 45

];

Now I would need something like

concatenate

load Period,

       "COGS in % of Sales" as Account,

       (load Value resident LoadData where Account = 'COGS') / (load Value resident LoadData where Account = 'Sales') as Value

Resident LoadData;

Any idea?

[jonathandienst · Accepted solution]

Hi

Try this

concatenate

load Period,

       "COGS in % of Sales" as Account,

       Sum(If(Account = 'COGS', Value)) / Sum(If(Account = 'Sales', Value)) as Value

Resident LoadData

Group By Period;

Regards

Jonathan

[Anonymous]

The 'COGS in % of Sales' must be in single quotes.

Regards,

Michael

[jonathandienst]

Oops