Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Gysbert_Wassenaar

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

View solution in original post

9 Replies
Gysbert_Wassenaar

Try sum(Value2) / sum(total Value1)


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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
Partner - Creator II
Partner - Creator II

Hi chadgambone,

You need to use the expression as follow :

sum(Value2)/ sum(total Value1)

Hope this helps you.

Regards

Gysbert_Wassenaar

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
Partner - Creator II
Partner - Creator II

Hi chadgambone,

I've updated your file.

Regads

CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

Try this expression for  'LR' column

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

using the keyword 'total' will help

Anonymous
Not applicable
Author

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