12 Replies Latest reply: Feb 18, 2013 2:00 PM by Felipe Carrera

IF and set analysis

hi

somebody knows how this IF-statement would look like in set analysis?

if(x= 'ab' and y='cd', 0, count distinct(z))

thanks for the help

• Re: IF and set analysis

Hi,

It can be

if(x='ab' and y='cd', 0, Count(Distinct z))

or

Count(Distinct if(x='ab' and y='cd', z))

Only the 2nd may be translated in Set Analysis :

Count({<x={ab}, y={cd}>} Distinct z)

Hope that helps you

Martin Favier

• Re: IF and set analysis

hi Martin

thanks. I tried already that way. it works, but i want to set a condition to the intersection of x and y (two dimensions), where the values are "ab" AND "cd".

other values of the dimension are not showed if i use the set analysis, but i want to see everything. Like this:

the dimensions are X, Y and are assigned different values. I want that when X=ab AND Y = cd, then it should show zero, otherwise, it should count the rest and show them in the fields

Y = aY=c
Y=cd
X = e451212
X = u789874
X= ab121445

Final result shall look like

Y = aY=c
Y=cd
X = e451212
X = u789874
X= ab12140
• Re: IF and set analysis

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

• Re: IF and set analysis

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

• Re: IF and set analysis

Ok I understand,

so it's a little bit more complicated.

Have you tried this (if we consider ID as the Primary Key) ?

Count({<ID=E({<x={ab}, y={cd}>} ID)>} Distinct z)

This expression depends on your datamodel.

Hope that helps you

Martin Favier

• Re: IF and set analysis

thanks

i tried, but the result is nothing (the formula shows no errors, but i do not get any number, i thinking i am making a logical error)

my primary key in the data structure is contact_id, which is at the same time the field i am counting

i imagine with this formula, i am counting the total number of customers, excluding the customers which fall into the intersection of segments:

Count({\$<contact_id = e({<[Segment_1] ={'NewCustomer'},[Segment_2]={'LostCustomer'}>} contact_id) DISTINCT contact_id)

Segment_1 = X

Segment_2 = Y

and

'NewCustomer' = 'ab'

'LostCustomer' = 'cd'

the contact_id would be ID and Z

what is wrong?

• Re: IF and set analysis

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

• Re: IF and set analysis

no, they are distributed everywhere. this a customer migration table, so it is important to see how many customers migrated from a segment_1 in a previous period to a segment_2 in the next period. It may be that they stayed in the segment, and this will be reflected in the diagonal. If they migrated, either up or down in the segment, this will be reflected in the other fields.

If there are nulls, this is reflected in the new / lost customers row / column, and finally, i have the NULL / NULL combination, this is, when the customers are located in the intersection NewCustomer AND LostCustomer.

I got this solved, but i made an outer join of a duplicated table, with different names (for period and segment). then included an IF statment in the script to the the "NULL / NULL" combination and flag it. Then in the expression i used set analysis on only one field (the flagged one) and it worked.

the problem is, i have now more than 4 billion records and the performance is bad. Aggregating data previously in SQL, last as well and seems not to be better., so i thought there may be a way with set analysis directly in the chart

hopefully it is clear, thanks for your help

felipe

• Re: IF and set analysis

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

• Re: IF and set analysis

Are all your contact_id in NewCustomer AND LostCustomer ?

Martin Favier

• Re: IF and set analysis

Hi,

Can you please provide test data in excel,what ever formate you are getting.

Regards

h

• Re: IF and set analysis
hi
the table structure is like this:
table:
month,
s
egment;
SQL SELECT *
FROM DATABASE;
i duplicated this table like (previously store as table.qvd):
table1: