Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

carlcimino
Not applicable

Multiply across Rows

Trying to multiply the Rate Change where Data Type = RATE to the Statutory Written Premium where data type = STAT by Year.  The Dollar Change results below the data are what I get in excel.  Any help would be appreciated.

    

YearRate ChangeStatutory Written PremiumData Type
2010-0.013$0RATE
20120.056$0RATE
20130.086$0RATE
20140$0RATE
20140.04$0RATE
2015-0.008$0RATE
20160.03$0RATE
2009-$112,404,000STAT
2010-$114,932,000STAT
2011-$119,944,000STAT
2012-$127,579,000STAT
2013-$135,964,000STAT
2014-$143,446,000STAT
2015-$147,480,000STAT
2016-$148,946,797STAT
Dollar Change
2009 -  
2010 (1,494,116)
2011 6,716,864
2012 10,971,794
2013 -  
2014 5,737,840
2015 (1,179,840)
2016 4,468,404
Tags (1)
3 Replies
eduardo_dimperi
Not applicable

Re: Multiply across Rows

Hi,

You could do this in your script

AUX:

LOAD

Rate Change,

Data Type

RESIDENT YOURTABLE

where  Data Type = RATE;



LEFT JOIN (AUX)

LOAD

Statutory Written Premium,

Data Type

RESIDENT YOURTABLE

where  Data Type = STAT;


FINAL:

LOAD

*,

Rate Change *Statutory Written Premium AS Dollar Change

RESIDENT AUX





eduardo_dimperi
Not applicable

Re: Multiply across Rows

Maybe

Set Analysis:

//Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})


AGGR(

SUM({<Data Type={RATE}>}Rate Change)

*

SUM({<Data Type={STAT}>} Statutory Written Premium)

,YEAR)

carlcimino
Not applicable

Re: Multiply across Rows

This worked as expected.  Is there any performance hit by doing it via the aggr function versus doing it in the script with resident loads and joins?