8 Replies Latest reply on May 19, 2015 3:51 PM by Josh Kennedy

    calculate percent of returning customers

    Pat Grady

      Hi! I work for a grocery store and we're expirementing with online sales. We created a very simple database to record the sales and I've hooked Tablaeu directly to that database to build a dashboard for our business users to gain insights into sales and customer behavior.

       

      I'm having trouble thinking through how to calculate a specific requirement:  "What is the % of returning customers who order?"

       

      This seems easy for me to calculate on my own, and I could TELL them what the number is, however, they want this value in a widget on their Tableau dashboard. To calculate by hand the %, I would do this psuedo math:

       

           What is the % of returning customers who order?

           [count of customers whose order count is > 1] / [count of customers] x 100 = [% of returning customers]

       

      I tried to solve in Tableau by creating a calculated field, however I'm having trouble figuring out how to make the calcuation understand the "whose" clause in my psuedo math. For example, I tried this:

       

      Calculated1 =

      IF COUNTD([cust_id]) > 1

      THEN

      COUNTD([cust_id])

      END

       

      Calculated2 =

      [Calculated1] / COUNTD([cust_id])

       

      This is failing terribly, because the result is 100% return customers, even though it is clear in the data that only 50% of the customers are returning. Unfortunately, I just can't wrap my head around how to do this calculation in Tableau.

       

      My interim solution is to write a seperate database table that will store the calculated values via a SQL script that I'll have run as a scheduled job on the server. I'll then include that table in the datasource and just basicly print that value to Tableau. That just seems like a hack, and that Tableau should be able to do this kind of thing on its own :-/

       

      Any tips, clues, admonishment, etc... are welcome!

      thanks!

      pat