Set analysis Equivalent

[#1]

Hi all

I just want to ask what is the equivalent of this expression in set analysis:

Sum(if(DestServiceProvider=Carrier and Type ='OB',Duration,0))

where Carrier is another field

attached is a sample qvw

Thank You

[Anonymous]

Hi,

Try this:

=Sum({<DestServiceProvider=Carrier, Type={'OB'}>}Duration)

[#3]

thank you for your reply but the answer that you gave requires a selection before it show the correct data.

[Anonymous]

Try this :

=Sum({<Type={'OB'}>}if(DestServiceProvider = Carrier,Duration,0))

[#5]

I just want to know if this expression will not affect the performance of my dashboard because it still has an if statement.. with 50M worth of records??

thanks

[Anonymous]

Sorry I am not sure about the performance

[johnw]

So far as I know, without doing something rather complicated (link below), the second expression Leni provided is as good as it's going to get given your data model.  The problem is that set analysis is not sensitive to your dimension values.  On each row, there's a specific Carrier, and you want your set to ONLY include that carrier for that row.  Instead, the set will include all selected Carriers, and thus isn't doing the match you want.

The right way to solve this problem is probably with data model changes to link your data more explicitly.

If there's a good reason not to do that, and performance with the sum({...} if(...)) isn't acceptable, have a look at this:

http://community.qlik.com/docs/DOC-1335