topic Re: Condition sum in QlikView

Condition sum

Thu, 30 Jun 2011 14:04:07 GMT

Hi all,

any one can help me out of this question:

Say I have a table like this:

LOAD * INLINE[Tab,Name,Num

Tab1,A,1

Tab1,B,2

Tab2,A,3

Tab2,C,4];

I need a pivot table like this:

dimension Name

expression:

if Name in Tab1 and not in Tab2 , calculate sum

so the pivot table is :

Name Num

A 0

B 2

C 0

Thanks in advance

Dihui

Condition sum

Thu, 30 Jun 2011 14:51:25 GMT

The obvious answer is to try using set expressions i.e. sum({$<Tab={Tab1}>}Num). But maybe your example data is oversimplifying this. The set expression will just given sum where Tab in in the set of values {Tab1}. Your sampel results don't seem to match the expression you have given as the sum for A would be 1?

Condition sum

Thu, 30 Jun 2011 15:16:34 GMT

Thanks for your answer ajohnson,

but you are not answering my question.

A is in Tab1 and also in Tab2, so not calculated.

Condition sum

Thu, 30 Jun 2011 16:25:11 GMT

This is pretty ugly but works! Add two expressions:

if(index(concat(Tab),'Tab2')=0,sum(Num),0)

concat(Tab)

The 1st expression concatenates all of the Tab values for a name (e.g. for A = 'Tab1Tab2') and then uses index(,'Tab2')=0 as the condition to return 0 if Tab2 appears in the string.

The second expression ensures that you get a row for each name in your table. You can hide the second expression for the table by setting 'Hide Column' in the presentation tab.

Re: Condition sum

kaushiknsolanki — Fri, 01 Jul 2011 07:29:12 GMT

Hi,

Have a look at this.

I hope this is what you want.

Find the attachment.

Regards,

Kaushik Solanki

Re: Condition sum

Miguel_Angel_Baeyens — Fri, 01 Jul 2011 08:17:49 GMT

Hello Dihui,

Check the following expression, that should sum the values in Num where the Name is in Tab1 but not in Tab2, using set analysis

Sum({< Name = P({< Tab = {'Tab1'} >}), Name = E({< Tab = {'Tab2'} >}) >} Num)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica