7 Replies Latest reply: Aug 21, 2017 10:55 AM by bruno bertels

# How to use a Sum If in Qlik Sense

I use this formula in excel so i can calculate the cumulative frequency (like a Pareto), i need that numbers with a value less than 60% in red, between 60% and 90% yellow and more than 90% in green.

=SUMIF(\$B\$2:\$F\$6,">="&B2)/SUM(\$B\$2:\$F\$6)

PD

=if(sum(Total)=0,white(),

colormix1(sum(Total)/\$(=max(aggr(sum(Total),LOSS))),Green(),red())) is not useful because it doesnt go through yellow and it's not so exactly.

And neither this formula works

=if(sum(Total)=0,white(),

if(rangesum(above(sum(Total),0,rowno()))/sum(TOTAL Total)<0.6,red(),

if(rangesum(above(sum(Total),0,rowno()))/sum(TOTAL Total)>0.9,Yellow(),

green())))

• ###### Re: How to use a Sum If in Qlik Sense

try replacing ABOVE with BEFORE

• ###### Re: How to use a Sum If in Qlik Sense

Thank you but it is still not working.

• ###### Re: How to use a Sum If in Qlik Sense

could you post a sample qvf ?

• ###### Re: How to use a Sum If in Qlik Sense

Which direction are you accumulating? vertically or horizontally?

• ###### Re: How to use a Sum If in Qlik Sense

I would like to compare the value against the total (as it could be seen in the excel); but i don't have really very clear how does the  RangeSum formula's work, what i need it's closer to the Colormix formula

• ###### Re: How to use a Sum If in Qlik Sense

Hi

Not sure it will helpfull but try this

if(sum(Total)=0,white(),

ColorMix1( hrank( total column(1))/  noofrows(TOTAL), lightred(), LightGreen()))

regards

Bruno