Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have this (simplified) table of transactions with CustomerID, OrderID, Date and Amount. The table contains records for the last ten years.
For each year, I would like to count the number of distinct CustomerIDs that didn't generate any business that year (sum(Amount) zero or Null), but that did business during the previous year, e.g. counting lost customers for each year. The inverse is the number of new customers for every year.
I tried around ten different variations on three themes:
Does anyone have a suggestion about how this can be accomplished? Thanks.
Peter
Hi Peter
If you create a loosen table with the years
load
distinctyear
(Date) as SelectedYear
resident
You can use this to calculate
Table;count
(if(year(Date) = SelectedYear -1,CustomerID))
-
count
(if(year(Date) = SelectedYear ,CustomerID))
Anders
Ok, that works if we were going to select a specific year before showing the results. I guess that would work with set analysis as well, as there is only one year to show at a time.
Unfortunately, the end result should be a chart with Year as a dimension and Lost customers as an expression.
Peter
Hi Peter
You can use SelectedYear as a dimension and the expression in a chart.
Anders
Hi Anders,
your code works if I add 'distinct' in each count(). So far so good.
However, this function only plots the variation in number of customers (#customers CY - #customer LY), not the amount of customers lost. Your solution will return 0 for a year when 5 existing customers are replaced with 5 new customers. But actually, the data point should be -5.
IMHO that makes it a lot more complex, don't you think?
Peter