1 2 Previous Next 22 Replies Latest reply on Mar 21, 2017 11:57 AM by Leanne Vermeulen

# Show Count of Items Ordered for First Time Purchases Only

Hey guys,

I'm struggling to replicate the chart below, but to show the count of items ordered [Item] for first time purchases only, instead of the sum of sales per Item for first purchases only. So the LoD expression I created for First Purchases is being used in the filter and works fine when calculating the SUM of Item sales for those days (by [Cust ID] - aka Customer ID). But no matter what calculations I create, I cannot get accurate results for the amount of times an Item was purchased, for First Purchase only.

Any ideas? Thanks, guys!

• ###### 1. Re: Show Count of Items Ordered for First Time Purchases Only

Hi Leanne,

When you say "the amount of times an item was purchased", what exactly do you mean? For example, if, for my first order, I order 5 of item #123, should that count as 1 or as 5?

Also, I noticed that some invoices list the same item twice. For example, invoice #1 lists item 1937 in two separate rows. One row has [# Of Items]=1 and the other has [# Of Items]=5. For this invoice, should it contribute 1, 2, or 6 toward this item's count?

Just trying to make sure I understand what you need so I can try to help

Michael

• ###### 2. Re: Show Count of Items Ordered for First Time Purchases Only

Leanne,

First, you shouldn't filter on dates,  because if you filter-in items with dates of  first order, it would also filter-in items with  similar dates  on  second order.

Anyway, on the filter you don't have to filter anything else than the top X items.

What you want is to compute the first Order date by CustId,  AND by Item  with

{FIXED [Cust ID],[Item]:MIN ([Invoice Date])}

Next you can flag each row with a 1 if it's a first Order Item with

if [Item] =

if [Invoice Date] = [First Order Date (by CustID,Item)] then [Item] end

then 1 else 0 end

Finally, bring the SUM of this latest calculation on the Label shelf.

see in the attached if you get the expected values (sheet Validate Data and Final View)

Michel

1 of 1 people found this helpful
• ###### 3. Re: Show Count of Items Ordered for First Time Purchases Only

I also noticed that there are some Customers who have the two different invoices on the same day (example: 45821 has 2 invoices on Jan 2, 2015). Should these both count as "first order"?

Either way, I don't think you actually need a LOD calculation for this. I think you only need the LOD calculation to filter out the "not first order" invoices. Once you've applied that filter, can you use a regular calculated field to get what you want. (Depending on your answer to the above questions, I think you'd want COUNT([Item]), COUNTD([Item]), or COUNT([# Of Items]).

• ###### 4. Re: Show Count of Items Ordered for First Time Purchases Only

Hi Michel, that's a great point about the dates filter. I wondered about that so thank you!

I'll try out your calculation and let you know if it's accurate; although I'm sure it will be

• ###### 5. Re: Show Count of Items Ordered for First Time Purchases Only

Hi Michael, eventually I will calculate the amount for item #123 (as per your example), but for now I just want to count that as 1, but only because it's part of the same invoice. Hope that makes sense?

As for the duplicates... there must be something wrong with my calculation if that's showing up and that probably has to do with filtering on dates as Michel mentioned. Regarding this question: "I also noticed that there are some Customers who have the two different invoices on the same day (example: 45821 has 2 invoices on Jan 2, 2015). Should these both count as "first order"?" That's a very good point - I didn't even think of that, thank you! That would definitely be a complication as it would have to be the first order only, even if it was on the same day.

Thanks!

• ###### 6. Re: Show Count of Items Ordered for First Time Purchases Only

You may want to go with the following,     -- if you want to take into account only the item of the first order (when multiple orders on same day)--.

This way you get all dates out of the equation.

First Invoice (by item,CustID)

{FIXED [Item],[Cust ID]: MIN( [Invoice] )}

and

if [Item] = if [Invoice] = [First Invoice (by item,CustID)] then [Item] end then 1 else 0 end

• ###### 7. Re: Show Count of Items Ordered for First Time Purchases Only

Hi Leanne,

Yep, makes sense. And Michel makes a great point.

To clarify, though, if I order Item #123 today (my first order) and Item #456 tomorrow, do you only want to count 1 for #123 (since #456 wasn't on my first order) or for both #123 and #456 (since it was the first time I ordered #456)? I believe Michel's LOD calculation will include #456 in the count.

To account for two invoices on the same day: are invoices always in numerical order? As in, if I place an order now and an order 2 hours from now, will the second invoice number be a higher number than the first? If so, you can use invoice number as a substitute for date: {FIXED [Cust ID]: min([Invoice])}. The only slightly oddity is that it looks like there are some Invoice numbers that have multiple dates (e.g., #35 has Feb 27 and Mar 1 Invoice Dates). If you only want the first date, you might want to use both Invoice Number and Invoice Date.

• ###### 8. Re: Show Count of Items Ordered for First Time Purchases Only

You're correct - I only want #123 because I'm interested in first order only. Michel's latest calculation suggestion is like this one you've suggested, but I'm still get some of those figures as being slightly higher by about 6 digits. I'm not sure how to fix it to be honest.

Regarding invoice in numerical order, I honestly don't know... I will have to query this with the client. But for now we can assume that they are ordered that way.

• ###### 9. Re: Show Count of Items Ordered for First Time Purchases Only

I think if you take out [Item] from the LOD calc you should be good

• ###### 10. Re: Show Count of Items Ordered for First Time Purchases Only

This is what the figures should be, but it's not quite happening:

# 35694: 23

# 41636: 27

# 35693: 27

# 39969: 41

# 10964: 65

• ###### 11. Re: Show Count of Items Ordered for First Time Purchases Only

Hmmm.... Where are you getting those figures from?

• ###### 12. Re: Show Count of Items Ordered for First Time Purchases Only

if you look in your dataset , you have some duplicate rows. For example, the Item 35693 have a couple of duplicate rows, in one case a triplet.

 40155 61 3/1/2014 35693 3/1/2014 10 \$591.91 0 \$591.91 61 35,693 1 550 550 40155 61 3/1/2014 35693 3/1/2014 10 \$591.91 0 \$591.91 61 35,693 1 550 550 40155 61 3/1/2014 35693 3/1/2014 10 \$591.91 0 \$591.91 61 35,693 1 550 550

If you change the  Item First Ordered with the following, you should get closer.

{FIXED [Item],[Cust ID],[Invoice]:MIN(

if [Item] = if [Invoice] = [First Invoice (by item,CustID)] then [Item] end then 1 else 0 end

)}

By removing the duplicates I get only 25 remaining rows for item  35693, I don't see how you get 27

Michel

• ###### 13. Re: Show Count of Items Ordered for First Time Purchases Only

I've calculated the values in Excel (I couldn't simply do this with the full dataset as it's 100's of 1000's of rows, which is why it has to be done in Tableau and am only using a subset for the workbook I've attached). This is what I've got so far:

Perhaps I'm making mistakes with the Excel calculations, but after removing these blue and green items, I count the number of cells per item and that's how I'm getting the figures. I'll attach it as well so you can see.

• ###### 14. Re: Show Count of Items Ordered for First Time Purchases Only

If you filter the excel file on Item and keep only 35693,  you have 32 rows (the value I got first),  minus 7 blue rows  = 25, which is what I get with the latest calculation.

I attached the workbook with this calculation.

1 2 Previous Next