Previous and Group By VARIABLE calculation in 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:

RESULT= RangeSum(Above(sum(aggr(sum(Conversion_rates) * sum(Produccion), Action_type,Date)),0,7))

 EXPECTED RESULT:

I need a SCRIPT solution, I know that Aggr function will not operate in the SCRIPT and will need to use Group By and also ABOVE in script is PREVIOUS, but I need help on how to handle it.

The RESULT value will be related to the date, so each date has it´s own value

Date                   Result

01/02/2019       x

02/02/2019      x1

....          ...

Regards

[lfetensini]

Conversion_rates:
Mapping LOAD
 Action_type as Conversion_rates_1, 
 Conversion_rates as Conversion_rates_2
FROM: .... .xlsx

Production:
LOAD
 Date,
 ApplyMap('Conversion_rates', Action_type, 1) * Production as Result
FROM .... xlsx

[luisccmm]

Thanks @lfetensini ,

But I do not see how to apply the set analysis formula requested in the script.

RESULT.FUNCTION= RangeSum(Above(sum(aggr(sum(Conversion_rates) * sum(Produccion), Action_type,Date)),0,7))

I may explain myselft not properly, but...

So the expected result is a table like the following, where all dates has a result for that RESULT.FUNCTION.

Date                               Action_type            Result

01/02/2019                 Action1                     RangeSum(Above(sum(aggr(sum(Conversion_rates) * sum(Produccion),Action_type,Date)),0,7))

01/02/2019                Action2                     RangeSum(Above(sum(aggr(sum(Conversion_rates) * sum(Produccion),Action_type,Date)),0,7))

01/02/2019                Action3                     RangeSum(Above(sum(aggr(sum(Conversion_rates) * sum(Produccion),

 Action_type,Date)),0,7))       

..........                                                 .........