3 Replies Latest reply on Jun 6, 2018 3:48 PM by Yeok Song Ng

Picking up New Accounts in FY2019

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

• 1. Re: Picking up New Accounts in FY2019

Hi Yeok,

Please see the attached workbook, I have created a calculation which works out the fiscal year:

Fiscal Year

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

1 of 1 people found this helpful
• 2. Re: Picking up New Accounts in FY2019

WOW! Thank you. This solves my problem. Mavis, you are amazing!

• 3. Re: Picking up New Accounts in FY2019

there was slight logic error. anyway, i updated it and upload here for completeness.