4 Replies Latest reply on Oct 18, 2018 10:15 AM by Roger Pouly

# Identify customers that belongs in several groups

Hello everyone,

I want to be able to identfy customers that :

Exclusively ordered in the summer.

Exclusively ordered in the winter.

Ordered both in summer and winter (in at least two different orders out of their XX orders).

As well as being able to identify customers that :

Exclusively ordered from the channel "WEB"

Exclusively ordered from the channel "STORE"

Ordered both in the channel "WEB" and "STORE"

I am having trouble creating a calculating field that is able to get those informations.

The field I have : [Order_number], [Client_number], [Order_channel], [Date_Order] and [Summer_or_Winter] (based on the date).

I tried to do something like : {Fixed [Client_number] : IF Order_channel = "WEB" AND Order_channel = "STORE" THEN 'Client Store and Web' END} but it obviously doesn't work.

Thanks a lot,

Roger

• ###### 1. Re: Identify customers that belongs in several groups

You can build up and debug your LoD expressions as follows:

1. Client Orders from Store

{ FIXED [Client Number]: ZN(SUM(IF [Order Channel]="STORE" THEN 1 END)) > 0 }

2. Client Orders from Web
{ FIXED [Client Number]: ZN(SUM(IF [Order Channel]="WEB" THEN 1 END)) > 0 }

3. Client Type
IF [Client Orders from Store] AND [Client Orders from Web] THEN "Both"
ELSEIF [Client Orders from Store] THEN "Store"
ELSE "Web"
END

Repeat/combine for summer/winter analysis.

10.4 TWBX attached.

1 of 1 people found this helpful
• ###### 2. Re: Identify customers that belongs in several groups

Thanks a bunch Zach Leber, that's actually a great solution.

Would you know if there is a way if I want to check specifically if the first order from a customer is from web, and the second (or third, or fourth) from store ? Meaning that I would know how many customers have been "converted" into using the store after discovering us via the web (or vice-versa).

Roger

EDIT : Thinking about it, I think I got it. I do not have access to Tableau until tomorrow so I cannot test right now, but something like :

1. First date Client Orders from Store

{ FIXED [Client Number]: IF [Order Channel]="STORE" THEN min([Date_Order]   END}

2. First date Client Orders from Web
{ FIXED [Client Number]: IF [Order Channel]="WEB" THEN min([Date_Order] END}

3. Web to Store conversion
IF [Client Orders from Store] AND [Client Orders from Web] THEN

IF [First date Client Orders from Store] > [First date Client Orders from Web]
THEN 1 END END

• ###### 3. Re: Identify customers that belongs in several groups

Once you get the hang of LoD expressions you can start to ask and answer lots of these questions. Your logic is correct but your syntax needs to be tweaked. Remember that a FIXED LoD expression looks at all the records for the fixed dimension so needs to apply an aggregation.

1. First Store Order

{ FIXED [Client Number]:
MIN(IF [Order Channel]="STORE" THEN [Order Number] END) }

2. First Web Order

{ FIXED [Client Number]:
MIN(IF [Order Channel]="WEB" THEN [Order Number] END) }

3. Conversion

// for clients with both types of orders, check what the first order type was

IF [Client Type]="Both" THEN
IF [First Web Order]<[First Store Order]
THEN "WEB > STORE"
ELSE "STORE > WEB"
END
END

Updated TWBX attached (version 10.4 for real this time).

1 of 1 people found this helpful
• ###### 4. Re: Identify customers that belongs in several groups

Thanks a lot for your advice, and for the calculations. I still have a lot to learn and that was super helpful.

The main thing I need to stop doing is that every time I try to do a calculation, to try to do it in a single calculated field instead of doing it step by step in multiple calculated fields, the right way.

Anyway, thank you again for the calculations and the workbook.

Roger