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

    Picking up New Accounts in FY2019

    Yeok Song Ng

      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
          Mavis Liu

          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:


          IF DATEPART('year', [Fiscal Year] ) = DATEPART('year', {FIXED : MAX([Fiscal Year])})

          then 'NEW'

          else 'OLD'



          Another to work out the min date if it's new:

          Datediff NEW DATE

          {FIXED [Account Number]: min(if [New?] = 'NEW'

          then ([Order Date])



          Another to work out the max date if it's old:


          Datediff PREV DATE

          {FIXED [Account Number]:

          max( if [New?] = 'OLD'

          then [Order Date]




          A date diff calculation which works out the number of days between the new date and the old date:


          DATEDIFF('day',[Datediff PREV DATE], [Datediff NEW DATE])


          Then flagging the ones which are truly new accounts or old accounts:


          if isnull([Datediff PREV DATE]) and not ISNULL([Datediff NEW DATE]) then 1


          if isnull([DATE DIFF]) then 0

          elseif [DATE DIFF] >= 0 and [DATE DIFF] < 365 then 1

          else 0




          I made quite a few fields, but really you can combine them together to make less calculations.


          Ultimate the two new accounts are:







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

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

            • 3. Re: Picking up New Accounts in FY2019
              Yeok Song Ng

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