Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

chadgambone
New Contributor II

Expression to divide one column by the total of another

I have data formatted like the first 3 columns in a pivot table, and I want to create the fourth:

YearValue1Value2Expression
Other-15,0007.5%
2011-50,00025.0%
2012-40,00020.0%
2013200,00030,00015.0%
Total200,000135,00067.5%

For example, the 7.5% is 15,000 (Other Years Value2) / 200,000 (Total Value1).

How do I accomplish this?  Thanks.

1 Solution

Accepted Solutions

Re: Expression to divide one column by the total of another

Ok, two simple sum expressions. You need the total keyword in the one you divide by;

=sum(if(DataType='Actual' and Measure = 'IncdLoss',Total,0))/sum(total if(DataType='Actual',EP,0))

Actually, you can rewrite your expressions so you don't have performance inhibiting if's:

sum({<DataType={'Actual'},Measure={'IncdLoss'}>} Total) / sum({<DataType={'Actual'}>} total EP)

See attached qvw


talk is cheap, supply exceeds demand
9 Replies

Re: Expression to divide one column by the total of another

Try sum(Value2) / sum(total Value1)


talk is cheap, supply exceeds demand
chadgambone
New Contributor II

Re: Expression to divide one column by the total of another

This is just giving me 0 everywhere.  Both value1 and value2 are expressions themselves, I should have mentioned that at the start.  They aren't just fields.

chadgambone
New Contributor II

Re: Expression to divide one column by the total of another

This is a slightly simplified version of it.  I need the LR column to all divide by the sum of EP, instead of the EP in that particular line.

jeffmartins
Contributor II

Re: Expression to divide one column by the total of another

Hi chadgambone,

You need to use the expression as follow :

sum(Value2)/ sum(total Value1)

Hope this helps you.

Regards

Re: Expression to divide one column by the total of another

Ok, two simple sum expressions. You need the total keyword in the one you divide by;

=sum(if(DataType='Actual' and Measure = 'IncdLoss',Total,0))/sum(total if(DataType='Actual',EP,0))

Actually, you can rewrite your expressions so you don't have performance inhibiting if's:

sum({<DataType={'Actual'},Measure={'IncdLoss'}>} Total) / sum({<DataType={'Actual'}>} total EP)

See attached qvw


talk is cheap, supply exceeds demand
jeffmartins
Contributor II

Re: Expression to divide one column by the total of another

Hi chadgambone,

I've updated your file.

Regads

Re: Expression to divide one column by the total of another

Hi,

Check the below expressions

Eprem: =Sum({<DataType={'Actual'}>} EP)

IL : =Sum({<DataType={'Actual'}, Measure={'IncdLoss'}>} [Total])

LR: =IL/Sum(TOTAL {<DataType={'Actual'}>} EP)

Hope it helps

Celambarasan

Highlighted
Not applicable

Re: Expression to divide one column by the total of another

Try this expression for  'LR' column

=IL/(sum (total (if(DataType='Actual',EP,0))))

using the keyword 'total' will help

chadgambone
New Contributor II

Re: Expression to divide one column by the total of another

Thanks everyone.  It took a little bit of finessing, but these techniques got it done.