Set analysis need to add condition

[dandaanilreddy]

Hello Qlik Developers

I have the below expression. I want to calculate this for Regions "Direct" and "IVF" only. Can someone help me.

=num(

sum(if(Reported_Out_actual.count_idTBD_actual<>0 and Reported_Out_actual.BillType='Awaiting Order Entry',Reported_Out_actual.count_idTBD_actual))

,'###,###')

Thanks

Anil

[sunny_talwar · Accepted solution]

May be this:

=Num(

Sum({<Region = {'Direct', 'IVF}>} If(Reported_Out_actual.count_idTBD_actual<>0 and Reported_Out_actual.BillType='Awaiting Order Entry',Reported_Out_actual.count_idTBD_actual))

,'###,###')

[dandaanilreddy]

Thanks Bro

[johnw]

Or this, with the if() converted to set analysis as well. And it seems we don't need to check for the Reported_Out_actual.count_idTBD_actual not being 0, since adding a 0 won't affect the sum?

num(sum({

<Region={'Direct','IVF'}

,Reported_Out_actual.BillType*={'Awaiting Order Entry'}>}

Reported_Out_actual.count_idTBD_actual))

,'###,###')

[johnw]

Or even do similar in script if all these fields are on a single table, and you have the memory for it, and script time is less critical than chart time.

if(match(Region,'Direct','IVF')
and Reported_Out_actual.BillType='Awaiting Order Entry'
,Reported_Out_actual.count_idTBD_actual) as [Direct/IVF Awaiting Order Entry Count]

Then no set analysis or if() in the chart.

num(sum([Direct/IVF Awaiting Order Entry Count]),'###,###')