
I want to count the number of new Account in FY2019
The definition of new account in FY2019 is
1. There must be a sale in FY2019
2. The date difference between first order date in FY2019 and previous order date is < 365 days
In the example below
Account X123 is existing account
Account X333 is existing account (because no sale in 2019)
Account X567 is New account
Account X777 is New account
My FY starts on 1st May
If possible, i prefers not to have any filter on the shelf. However, if that is not possible, filter on shelf is also fine
Hi Yeok,
Please see the attached workbook, I have created a calculation which works out the fiscal year:
Fiscal Year
MAKEDATE(DATEPART('year',DATEADD('month',8,[Order Date])),1,1)
Then another field which flags whether it appears in FY 2019:
New?
IF DATEPART('year', [Fiscal Year] ) = DATEPART('year', {FIXED : MAX([Fiscal Year])})
then 'NEW'
else 'OLD'
end
Another to work out the min date if it's new:
Datediff NEW DATE
{FIXED [Account Number]: min(if [New?] = 'NEW'
then ([Order Date])
END)}
Another to work out the max date if it's old:
Datediff PREV DATE
{FIXED [Account Number]:
max( if [New?] = 'OLD'
then [Order Date]
END)}
A date diff calculation which works out the number of days between the new date and the old date:
Datediff
DATEDIFF('day',[Datediff PREV DATE], [Datediff NEW DATE])
Then flagging the ones which are truly new accounts or old accounts:
Datediff>365
if isnull([Datediff PREV DATE]) and not ISNULL([Datediff NEW DATE]) then 1
else(
if isnull([DATE DIFF]) then 0
elseif [DATE DIFF] >= 0 and [DATE DIFF] < 365 then 1
else 0
END)
END
I made quite a few fields, but really you can combine them together to make less calculations.
Ultimate the two new accounts are:
Thanks,
Mavis