Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Previous and Group By VARIABLE calculation in SCRIPT

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

Labels (3)
2 Replies
Highlighted
Partner
Partner

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

Support your colleagues. Remember to "like" the answers that are helpful to you and flag as "solved" the one that helped you solve. Cheers.
Highlighted
Creator
Creator

Thanks @lftensini ,

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))       

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