Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
Thanks for your answer ajohnson,
but you are not answering my question.
A is in Tab1 and also in Tab2, so not calculated.
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.
Hi,
Have a look at this.
I hope this is what you want.
Find the attachment.
Regards,
Kaushik Solanki
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.
BI Consultant