I am trying to come up with a FIXED LOD calculation that labels if a [customer] (string field) stopped reporting. To do this, I want to count instances by month, using Month of [Purchasedate] in the columns shelf. I want the rows to state one of the following and count all instances that a [customer] falls in each month: "No Sales Previous Month - Existing Reporter", "No Sales Previous Month - New Reporter", or "Reported Sales in Previous Month".
I've tried a bunch of different calculations and am not getting the results that I expect. Any help writing this formula, if it is possible, would be greatly appreciated.
Hi Ken,
You may want to create a FIXED LOD to identify whether a customer had sales in the previous month, then use that result in a classification calculation.
For example, you could first calculate the customer’s previous reporting month:
{ FIXED [Customer] : MAX(DATETRUNC('month',[PurchaseDate])) }
Then compare it to the current month in a separate calculation to determine whether they are:
- Existing Reporter
- New Reporter
- Stopped Reporting
You may also need a table calculation such as LOOKUP() if you want to compare month-over-month activity dynamically in the view.
The combination of FIXED LOD + LOOKUP usually works well for this type of retention/churn analysis.
