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:
IF COUNTD([cust_id]) > 1
[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!