Multiple variables in set analysis or Script

[luisccmm]

I have the following two tables in the script.

Conversion_rates:

LOAD

Action_type,

Conversion_rates

FROM: .... .xlsx

Production:

LOAD

Date

Action_type,

Production,

FROM .... xlsx

There are around 40 Action Type and each of it has its own conversion rates (From table Converson Rates) I need to create a formula similar to the following:

Conversion_rates * sum( Production)   

So the function above multiples the value of the conversion_rate of the action_type by the production on a specific date.

Conversion_rates:

Action_type      Conversion_Rates

Action1                   0,2%

Action2                   0,3%

....

Action20                 0,66%

.....

Action40              0,4%

Production:

Date                    Action_type                  Production

01/02/19            Action1                               2000

01/02/19            Action20                            3000

....

04/02/19           Action 40                            5000

04/02/19          Action2                                7000

....

EXPECTED RESULT in a Date Table

Date                    Function_RESULT

01/02/19            2000*0.2% + 3.000*0.66%

04/02/19            5000* 0.4%     +7000*0.3%

Regards

[marcus_sommer · Accepted solution]

You need an aggr() to do it in this way within the UI like:

sum(aggr(Conversion_rates * sum( Production), Action_type))

Depending on the variety of the context in which this kind of calculation should be done and/or if there are further aspects, like changing of the factors over the time or similar things - it might be more suitable to map the factors to the production-table.

- Marcus

[luisccmm]

@marcus_sommer 

Hi Marcus,

Your answer seems perfectly right, just one thing.

I think that "date" is missing in your Aggr formula.

sum(aggr(Conversion_rates * sum( Production), Action_type, Date))

Because expected result is value for each date in a table like the following.

EXPECTED RESULT in a Date Table

Date                    Function_RESULT

01/02/19            2000*0.2% + 3.000*0.66%

04/02/19            5000* 0.4%     +7000*0.3%

Regards