Combining 'if' and 'sum'

[#1]

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.

[swuehl · Accepted solution]

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)

[arulsettu]

try like this

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

[jagan]

Hi,

Best method using Set Analysis without if

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

Regards,

Jagan.

[#5]

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.

[jagan]

Hi,

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

Regards,

jagan.

[MarcoWedel]

Set expressions should perform better

[#8]

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