Is this type of SET/IF analysis possible?

[#1]

Hi,

I have points in a process that I would like to count based on their text values. I have the process points stored as a transactional table, I would like to keep it this way rather than a cross tab, or flagging system in the load.

So I have customer ID's in a list, repeated with each point in a process that a customer has completed, ie:

Customer ID	Process Point
1	Telephone Enquiry
1	Order Complete
1	Order Shipped
2	Web Enquiry
3	Web Enquiry
3	Order Complete
3	Order Shipped

Can I get a count of how many customers have only submitted a Web enquiry, and have no "Order Complete" record? So above would reveal a count of 1.

Similarly, can I count how many web order have shipped, and how many telephone orders have shipped? This is what I have tried, but doesn't seem to be right. Again, there would be one of each of these using example above.

count(distinct {$<clienttable.DESC= {"Web Enquiry"}, clienttable.DESC={"Order Shipped"}>} Join_FACTTABLE)

Thanks in advance

[#2]

Hi

Use an if as you do not want to have a flag field that would allow for summarising

If you have the customer as dimension use

sum(if(clienttable.DESC="Order Shipped",1))

Regards

Juerg

[#3]

Hi Juerg

how does that work for the multiple conditions though?

Like in the examples above where another record exists/does not exist for the same client ID?

[#4]

Hi

Just "or" and "and" your conditions together

sum(if(clienttable.DESC="Order Shipped" or clienttable.DESC="WebInquiry",1))

[#5]

with the example above though, that would get me the wrong answer. It would count each entry of "Web Enquiry", plus every entry of "Order Shipped" - this would be 4.

I need to count the number of times both of these things appear for one customer reference, so the actual number of web enquiries that resulted in an order shipped, is 1.

[#6]

Hi

Something like this then?

if(
sum(if(clienttable.DESC="Order Shipped",1))>1
and
sum(if(clienttable.DESC="Web Enquiry",1))>1
, 1)

Regards

Jürg

[#7]

hmm, not sure why, but that expression doesn't seem to work at all?

[#8]

Attached is my attempt of your expression, am I doing something wrong?

[#9]

Hi

Sorry to make you wait. The condition has to be >0 of course. See my example.

The solution might also be to use Set Analysis but the syntax is rather confusing and you can spend a long time on trials.

Regards

Juerg

[#10]

Juerg,

That is great, thanks for your explanation. However, i think I need the set analysis way, so the join to my fact table can be made, and related to dates stored in it.

At the moment my count will work, but I can't link it to my data structure.

Thanks