topic Re: Set analysis Equivalent in QlikView

Set analysis Equivalent

Fri, 26 Aug 2011 05:05:54 GMT

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

Re: Set analysis Equivalent

Anonymous — Fri, 26 Aug 2011 06:17:45 GMT

Hi,

Try this:

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

Set analysis Equivalent

Fri, 26 Aug 2011 06:33:00 GMT

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

Re: Set analysis Equivalent

Anonymous — Fri, 26 Aug 2011 06:34:38 GMT

Try this :

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

Set analysis Equivalent

Fri, 26 Aug 2011 06:44:56 GMT

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

Set analysis Equivalent

Anonymous — Fri, 26 Aug 2011 07:40:00 GMT

Sorry I am not sure about the performance

Set analysis Equivalent

johnw — Fri, 26 Aug 2011 23:30:21 GMT

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