-
Re: IF and set analysis
Hi,
Your expression is wrong.
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
Header 1 Header 2 Header 3 Header 4 Y = a Y=c
Y=cdX = e 45 12 12 X = u 78 98 74 X= ab 12 14 45 Final result shall look like
Header 1 Header 2 Header 3 Header 4 Y = a Y=c
Y=cdX = e 45 12 12 X = u 78 98 74 X= ab 12 14 0 -
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
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
Hi,
Can you please provide test data in excel,what ever formate you are getting.
Regards
h
-
Re: IF and set analysis
hithe table structure is like this:table:LOADcontact_id,
month,
segment;
SQL SELECT *
FROM DATABASE;i duplicated this table like (previously store as table.qvd):table1:LOADcontact_id,
month as month_1,
segment as segment_1
FROM table.qvd (qvd);table2:LOADcontact_id,
month as month_2,
segment as segment_2
FROM table.qvd (qvd);i got an extra "contact" table including only my customers and their countriesI attached the excel sheet with the data structure
-