8 Replies Latest reply on Aug 10, 2017 7:37 AM by Katie Faulks

# Showing most recent purchased web name per customer

Help! I'm sure this should be really simple but I'm struggling. I have a list of customers who can buy as many web names as they like but each webname will be unique. I want to produce a list so I have customer ID and webname where I only have one record per customer and this is by most recent purchased webname.

So data looks like this:

 Customer WebName Order Date 1 x 27/03/2008 00:00 2 a 21/11/2016 00:00 2 b 15/08/2011 00:00 2 c 23/07/2015 00:00 3 d 01/07/2017 00:00 3 e 01/07/2017 00:00

And I want it to look like this

 Customer WebName Order Date 1 x 27/03/2008 00:00 2 a 21/11/2016 00:00 3 d 01/07/2017 00:00

Another problem is where there are two on the same day I want to just pick any one as don't want any duplicate customer IDs in there.

What's making it difficult is I want the calculation based on customer ID and date really but then once that is done I need it to return the correct webname based on that calcuation. Hope this makes sense.

• ###### 1. Re: Showing most recent purchased web name per customer

Hi Katie

This is an example where an LOD expression comes in handy

Have you tried           {fixed [customer],[web name]; Max(order date) }

Let me know how this works for  you

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Showing most recent purchased web name per customer

Hi Katie,

Have a look here, your problem seems really similar:

Regards,

Vincent

• ###### 3. Re: Showing most recent purchased web name per customer

Ah I thought LOD might help but couldn't work out how to use them. So do I create a calculated field with that expression in? Then what do I do with it? Sorry need a bit of talking through it

• ###### 4. Re: Showing most recent purchased web name per customer

By doing the exact same calculation as ZZ, here is the result:

Last web name purchased:

{ FIXED [Customer] : MIN(

IF [Order Date] = { FIXED [Customer]: MAX([Order Date]) } THEN [Web Name] END) }

Last order date:

{ FIXED [Customer] : MIN(

IF [Order Date] = { FIXED [Customer]: MAX([Order Date]) } THEN [Order date] END) }

Hope this helps

Regards,

Vincent

2 of 2 people found this helpful
• ###### 5. Re: Showing most recent purchased web name per customer

Ok Katie

I'm a little in the dark here -

The fixed LOD  will give you a single record for each customer/web name combination (think like the permutations of customer and web name ) and it will return the last order date for the combination - (last order date)

Now you can use the calculation in a number of ways - you can turn it into a boolean filter by and set it equal to True

you can just place it into your viz on rows with customer, web name and then your fixed calculation converted into a discrete Blue Pill

or you can use it as a test condition in an If statement - like if attr([date]) = {the name for your fixed calc goes here] then ...you can create different then clauses or else clauses to meet you need

Jim

• ###### 6. Re: Showing most recent purchased web name per customer

Hi Vincent,

THat works really well except I forgot to mention there are some webnames for customers we want to exclude so I have them in my excusions but they still show up as most recent in your calculations. Is there any way to exclude them?

• ###### 7. Re: Showing most recent purchased web name per customer

Simply drop your Web Name dimension as a filter and keep/exclude the web name you want. Then, right click on it and select 'Add to context', it will then filter the data as you'd like.

A link that might be useful to understand how Tableau works a bit:

1 of 1 people found this helpful
• ###### 8. Re: Showing most recent purchased web name per customer

Brilliant that now works!! Thank you very much