5 Replies Latest reply: Jul 1, 2011 4:17 AM by Miguel Angel Baeyens de Arce

# Condition sum

Hi all,

any one can help me out of this question:

Say I have a table like this:

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

Dihui

• ###### Condition sum

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

but you are not answering my question.

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

• ###### Condition sum

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

Hi,

Have a look at this.

I hope this is what you want.

Find the attachment.

Regards,

Kaushik Solanki

• ###### Re: Condition sum

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