topic Re: Combining 'if' and 'sum' in QlikView

Combining 'if' and 'sum'

Tue, 23 Jun 2015 08:42:35 GMT

Can I combine an 'if' and a 'sum' statement within a gauge chart? For example, if the office location = 'XXX' give me the sum of the Cases Closed Ontime / Total Number of Cases. At present, my expression reads...

=sum(TRCaseClosedOntimeCounter)/sum(TRJobCounter)

How do I build in the 'if' statement?

Thanks in advance.

Re: Combining 'if' and 'sum'

swuehl — Tue, 23 Jun 2015 08:49:17 GMT

Maybe like this:

=sum(if(Location = 'XXX', TRCaseClosedOntimeCounter))

/ sum(if(Location = 'XXX', TRJobCounter) )

or using set Analysis:

=sum({<Location = {'XXX'}>} TRCaseClosedOntimeCounter)/sum({<Location = {'XXX'}>}TRJobCounter)

Re: Combining 'if' and 'sum'

arulsettu — Tue, 23 Jun 2015 08:50:18 GMT

try like this

if(office location = 'XXX' ,sum(TRCaseClosedOntimeCounter)/sum(TRJobCounter))

Re: Combining 'if' and 'sum'

jagan — Tue, 23 Jun 2015 09:01:49 GMT

Hi,

Best method using Set Analysis without if

sum({<Location = {'XXX'}>}TRCaseClosedOntimeCounter)/sum({<Location = {'XXX'}>} TRJobCounter)

Regards,

Jagan.

Re: Combining 'if' and 'sum'

Tue, 23 Jun 2015 09:15:33 GMT

Thanks for the prompt responses. I'm still getting to grips with how formulas are constructed.

What's the difference between combining the 'if' and 'sum' statements and using Set Analysis?

Thanks again.

Re: Combining 'if' and 'sum'

jagan — Tue, 23 Jun 2015 09:16:55 GMT

Hi,

Both methods work, but Set Analysis will have better performance.

Regards,

jagan.

Re: Combining 'if' and 'sum'

MarcoWedel — Tue, 23 Jun 2015 09:18:26 GMT

Set expressions should perform better

Re: Combining 'if' and 'sum'

Tue, 23 Jun 2015 13:43:10 GMT

Thanks all... I've now set this up as a Set Analysis