I am trying to produce a report that is able to count the number of months with no sales and list it next to the customers name. I am also trying to identify new customers who are defined as customers with sales within the previous 12 months who had no sales between the previous 18 months and previous 12 months. Any help is greatly appreciated!
Matthew,
You need to use many LOD s to achieve the goal. // Overview: Level of Detail Expressions
[Previous 12 Months]
DATEADD('month',-12,{fixed : Max([Date])})
[Previous 18 Months]
DATEADD('month',-18,{fixed :Max([Date])})
[Max Date in the data]
{fixed :MAX ([Date])}
[Last Sales Date]
{Fixed [Customer #]:max([Date])}
[Previous 12 months Sales]
sum(IF [Date] >= [Previous 12 Months]
and [Date] <= [Max Date in the data] then [Sales] END)
[Previous 18 months Sales]
sum(IF [Date] >= [Previous 18 Months]
and [Date] <= [Max Date in the data] then [Sales] END)
[18 month - 12 month]
{fixed [Customer #]:[Previous 12 months Sales]}
-{fixed [Customer #]:[Previous 18 months Sales]}
[New Flag]
if [18 month - 12 month]=0 then "New" else "Existing" end
[No Sales Month]
datediff('month',[Last Sales Date],[Max Date in the data])
I think this works, but could you double check?
Thanks,
Shin
