Condition sum

[#1]

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

[#2]

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?

[#3]

Thanks for your answer ajohnson,

but you are not answering my question.

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

[#4]

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.

[kaushiknsolanki]

Hi,

   Have a look at this.

   I hope this is what you want.

   Find the attachment.

Regards,

Kaushik Solanki

[Miguel_Angel_Baeyens]

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