Skip to main content

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

Picking up New Accounts in FY2019

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

5 réponses
  1. 6 juin 2018, 13:56

    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:

    Hi Yeok, Please see the attached workbook, I have created a calculation which works out the fiscal year:Fiscal YearMAKEDATE(DATEPART('year',DATEADD('month',8,[Order Date])),1,1) Then another field whi

     

    Thanks,

     

    Mavis