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

    Potential Lost Customers

    David Wright

      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
          Shawn Wallwork

          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
            David Wright

            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
              Shawn Wallwork

              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
                Shawn Wallwork

                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
                  David Wright

                  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
                    David Wright

                    Calc Error.png

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

                    • 7. Re: Potential Lost Customers
                      David Wright

                      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
                        Shawn Wallwork

                        Sorry about that. So create these three calculations:

                         

                        Year Gift Date:

                        YEAR([Gift Date])

                         

                        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
                          David Wright

                          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
                            Shawn Wallwork

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

                             

                            --Shawn