I am identifying 4 metrics
Metric 1. Request - Count all unique ids
Metric 2. Enrolled - All customers that have a date. This confirms that the customer received orders
Metric 3. Current - Date is less than 6 months from today. This will confirm that the customers are active
Metric 4. Dropped - Date is more than 6 months from today, This confirms that customers did not buy from us for more than 6 months.
Calculation Summary
I am calculating the date difference and then using buckets like < 6 months and > 6 months to separate the data. Then using the individual calculated field to count the numbers for each metric.
Below are my current calculations in Tableau
Metric 1 : Request
countd(id)
Metric 2 : Enrolled
COUNTD(IF NOT ISNULL(
[Date])
THEN [ID]
END)
Metric 3 : To calculate Current Customers, I have below additional calculations.
1. Date diff calculation
if NOT ISNULL([Date])
then datediff('month',[Date],Today())
END
2. current six months Bucket
IF [Date Diff Calc]<=6 THEN "<6 months"
END
3. Current Customer metric
COUNT([current six months Bucket])
However, I need to make changes to - Metric 2 (Enrolled) and Metric 3 (Current) with additional conditions
Metric 2 : Enrolled
1. Customers that have 'QRST' prefix in their ID should only be counted when the Repeat column has 'No'
2. But for the rest of the customers, all rows should be counted regardless of repeat yes or no statuses.
3. Additionally, two IDs- QRST-AA2517 and QRST-CO1325 should be removed from the total count.
Metric 3 : Current
1. Customers that have 'ABC' as prefix in their ID and Country = countryname should not be counted under this metric
2. But for the rest of the customers, all rows should be counted regardless of the country
Sample data structure
ID DATE REPEAT COUNTRY
ABC-1234 12-3-2015 Yes USA
QRST-AA2517 11-5-2021 No Italy
XYZ - 1234 08-3-2022 No Germany