10 Replies Latest reply on Mar 29, 2016 3:41 PM by Shawn Wallwork

Potential Lost Customers

I have been struggling to come up with a calculation that would allow me to list all donors for a non-profit org who have contributions in the prior year, but do not yet have contributions in the current year. I have been manually doing this for several months now by listing all contributions for the PY and CY, then sorting Hi2Lo by the CY and MANUALLY EXCLUDING them from the worksheet. I am now needing to roll out this workbook for 3,000+ organizations and it is neither practical nor feasible to have to go in to each one  and manually adjust after each week's data update. I have attached a sample workbook using the Superstore data to roughly model out what I am looking for. Again, I need to EXCLUDE the current years contribution \$s so the potential lost sales \$ from the PY calculate correctly, not HIDE them. Thanks in advance for any assistance!

• 1. Re: Potential Lost Customers

Thanks for the workbook. But you didn't mention the version # (it's not 9.3) so I won't post a workbook you can't open.

I think you are looking for this calculation:

MAX(YEAR([Order Date])) = MAX(YEAR(TODAY()))

Put it on the filter shelf set to false. If they aren't equal, then the client hasn't placed an order for this year.

--Shawn

• 2. Re: Potential Lost Customers

Thanks so much for replying, SHAWN. I tried your solution. While it does EXCLUDE the CY Donations, it DOES NOT remove the CY Donors from the remaining list.

A simple sample below:

Original State

Donor          PY Donations       CY Donations

Donor A     \$10.00                    \$15.00

Donor B     \$0.00                       \$5.00

Donor C     \$25.00                    \$0.00

Desired State after Calculation Applied

Donor          PY Donations

Donor C        \$25.00

The solution you provided leaves Donor A in the list, along with their PY Donations. Donor A is not a potential lost donor. They have donated in the CY.

Donor C is a potential lost donor. They previously donated in the PY, but have not yet donated in the CY.

• 3. Re: Potential Lost Customers

What date did Donor A last contribute? If the max date of Donor A's contribution is the current year then it will be filtered out. If the max date of Donor A's contribution is last year, then why is \$15 showing up in the current year donations?

--Shawn

• 4. Re: Potential Lost Customers

Hang on I get what you're saying now. Try this:

IF  MAX(YEAR([Order Date])) <> MAX(YEAR(TODAY())) THEN [Donner] END

--Shawn

• 5. Re: Potential Lost Customers

Donor A last donated in the CY. I do not need just their current year's donation removed; I need Donor A's PY & CY donations removed, along with their name. I am looking for a calc that removes the name from the list if the PY donor has given in the CY.

The only names remaining are those donors who have given in the prior year, but have not yet given in the CY.

ALL PY Donors - CY Donors = Remaining Potential Lost Donors

• 6. Re: Potential Lost Customers

Getting a syntax error when I try to test out...

• 7. Re: Potential Lost Customers

I fixed the syntax error; but still have the prior issue remaining. Your solution removes just the CY donations, not the CY donor's PY contributions as well. See post time-stamped 2:29 pm. above.

• 8. Re: Potential Lost Customers

Sorry about that. So create these three calculations:

Year Today:

Year(Today())

Filter Donors:

IF { FIXED  [Last Name First] : MAX([Year Gift Date]) } <> { MAX([Year Today]) } THEN [Last Name First]  END

Using a LOD expression we can get around the agg/non-agg error. FYI: You have to create the first two calcs because T won't let you put a date calculation inside a LOD.

Cheers,

--Shawn

• 9. Re: Potential Lost Customers

Thanks so much, SHAWN, for working with this with me and arriving at the exact solution that I was looking for! Tableau Zen Masters come through yet again! Thanks for sharing your knowledge and time with everyone!

• 10. Re: Potential Lost Customers

Happy it worked for you! We Zens really do enjoy sharing our knowledge; spread the Tableau!

--Shawn