4 Replies Latest reply: Sep 24, 2014 6:18 PM by Simen Kind Gulbrandsen RSS

    Counting Accounts Termed

       

      Hi,

      I’m
      looking to create a text item that counts how many users had their accounts
      termed in the previous month.

      I’m
      currently using this expression but the number it’s providing is off – it’s too
      high. When a user’s account is termed, their Account Status value changes from
      Active to Inactive. OwnerID is being used as a unique identifier for each
      user.


      =Count({$<[Date
      Termed]
      ={"<=$(=date(floor(monthend(addmonths(Today(),-1))))
      ),
      =>$(=date(floor(monthstart(addmonths(Today(),-1))))
      )"
      },
      [Account
      Status]
      ={'Active',
      'Inactive'}>}
      DISTINCT
      [OwnerID])


      Any
      suggestions?
        • Re: Counting Accounts Termed
          Manish Kachhia

          It would be better if you can provide some sample data...

          • Re: Counting Accounts Termed
            Vlad Gutkovsky

            While I don't know your data, I think a reason it's too high is that it doesn't take into account the order in which the Account Status changes (Active >> Inactive vs Inactive >> Active). In addition, specifying 2 values in set analysis like that is an OR operation, not an AND. Try this instead:

             

            =count({

            <[Account ID]=P({<[Date Termed]={"<=$(=date(floor(monthend(addmonths(today(),-1)))))>=$(=date(monthstart(addmonths(today(),-1))))"},[Account Status]={'Active'}>} [Account ID])>

            *

            <[Account ID]=P({<[Date Termed]={"<=$(=date(floor(monthend(addmonths(today(),-1)))))>=$(=date(monthstart(addmonths(today(),-1))))"},[Account Status]={'Inactive'}>} [Account ID])>

            } distinct OwnerID)

             

            This still won't take into account the order of Account Status changes. If that's a concern, I suggest you create a simple flag in the script that would be 1 when an account changes from Active to Inactive in a single month. You can use ORDER BY logic to create that.

             

            Replace Account ID in the above with your actual account ID field.

             

            Regards,

            Vlad

            • Re: Counting Accounts Termed

              This is the sample data and as you will see the count is 61. Sorry the attachment is not working so i just pasted the data.

              DirectoryUserIDFull NameResource ManagerOrganizationSupervisorDepartment NameResource TypeResource Type DescriptionCompany CodeWorking StatusAccount StatusIdentification NumberDirectorLocationJob FamilyPracticeTechnologyEnterpriseMemberIDDate HiredDate Termed
              F35BEC77-D6A8-4B82-A54E-896C0CF63DF7EmployeeInactive248620{DF9C4265-3359-45D8-BB7B-21D625E022AB}5/2/2011 0:009/15/2014 0:00
              25DD61B8-51B5-4CEA-9E2F-193041F9D8ABConsultantInactiveQ10191716{F021CDE3-6A26-46E4-82B5-451CE67F9FFB}6/16/2014 0:009/15/2014 0:00
              BFAD51EA-AF1B-440C-B8A3-1C432882A7AEFreelancerInactiveQ10160359{9823A362-3D95-41AC-8FED-57BD64A89851}9/16/2013 0:008/31/2014 0:00
              045DC6F9-14BD-491C-8381-8A572129D577EmployeeInactive249622{6BDF5AD2-B5C0-4328-B5E7-562F2D799D15}6/13/2005 0:008/29/2014 0:00
              E5DA4730-478E-4270-8808-6DC78036A254ConsultantInactiveQ10095808{F8E2AF8A-ED34-4FF1-BAA9-4771BCFF4C7E}6/2/2010 0:008/29/2014 0:00
              A8FD1220-9750-41F6-B3CE-986D422CF793EmployeeInactive251005{D34565A5-57B4-4D7C-B445-217C969B197A}10/25/2010 0:008/29/2014 0:00
              D364F80E-15E9-44C9-A2DB-FC45CF640826EmployeeInactive249123{FA17C50F-82A1-4D6D-A771-93F8C6C053BD}8/15/2011 0:008/29/2014 0:00
              010DCDD4-9A5A-43D9-9D35-3C3C506EFBD2EmployeeInactive250995{D571C14E-0CC0-46A8-ADD2-FFC96EC2A0CA}9/6/2011 0:008/29/2014 0:00
              BE938100-8F94-47E2-A7CC-EF05DFD7464CConsultantInactiveQ10187675{51D1D881-CFE4-4EAC-95E6-FD46043FA5DC}12/12/2011 0:008/29/2014 0:00
              15703C55-21C1-4995-9E75-B1441793CB74EmployeeInactive261378{2995FCCC-B8ED-4EC4-B93E-9BBA4B43B748}9/4/2012 0:008/29/2014 0:00
              0186177B-780A-4079-ADA8-89F6D1B9FECAConsultantInactiveQ10142861{06B7D147-0DEC-4906-9FB9-F81D7DCC5722}11/5/2012 0:008/29/2014 0:00
              D0495432-FDC7-4301-9705-D4A56356FE7AConsultantInactiveQ10145233{2BEEC3AF-8C13-4FF5-94C0-6CD96077CC5E}4/10/2013 0:008/29/2014 0:00
              C51F93FD-4CBD-4A8E-ABE3-C04448F53070FreelancerInactiveQ10159226{B86A7EBE-B9B0-480F-B260-A461569F2A3A}9/9/2013 0:008/29/2014 0:00
              CF1D47DD-B715-45FD-BA68-9BAB9F774B71FreelancerInactiveQ10160234{2463AD9E-75D0-4F6A-971D-DE47B7E90775}11/13/2013 0:008/29/2014 0:00
              F81A26E4-4521-441C-9B89-D25B8245C44DFreelancerInactiveQ10186075{6932BAE4-1CAE-41BF-853B-D1CE7BD3A219}4/14/2014 0:008/29/2014 0:00
              CAAE5D49-52D1-4616-B03C-A9184BF8CA58ConsultantInactiveQ10185602{785E7999-F115-4A5A-9A1A-966B760FA100}4/16/2014 0:008/29/2014 0:00
              A6DEA9D8-E8D1-4E26-B04B-26FAF79B9F4FConsultantInactiveQ10187977{1C5AAF09-80B9-4AE1-B933-09FD75654906}5/12/2014 0:008/29/2014 0:00
              B943B69B-D465-4F6F-BC49-7AB0FEFD3831ConsultantInactiveQ10189188{1EA2E303-5FCD-4730-AB65-EFF04AADD1B4}5/21/2014 0:008/29/2014 0:00
              BEB5CCC0-82EF-4A60-837F-D67DAE89B419ConsultantInactiveQ10191161{B3E07373-6F1E-4B7C-B83A-3CD763AE3685}6/9/2014 0:008/29/2014 0:00
              0CD764F8-1A42-40B5-9DCE-2C38F2FE5E9EEmployeeInactive251242{6748C666-7039-42E4-A328-6BEC66D5543E}9/15/2008 0:008/26/2014 0:00
              6AD26583-F993-4F22-BC1F-870CA32DE38BEmployeeInactive253061{C60C93B2-147F-427B-9379-1A7E58FFF429}11/3/2013 0:008/26/2014 0:00
              8D9AB515-7A0B-472C-8CDE-F50F40D31C97ConsultantInactive Q10146320 {6A582205-E440-48D6-820F-762B9DB4DC31}5/8/2013 0:008/22/2014 0:00
              A53A128A-50A0-45B1-9E71-4DD366F001BDEmployeeInactive273108{40D1956A-064A-4536-ACB0-DF9BB3DE4572}6/11/2013 0:008/22/2014 0:00
              4F9F47DA-9479-4E5A-A7B9-9330B76A5C93EmployeeInactive269715{6872EAF3-2C54-4436-BF4C-7F644C5A9DE9}7/15/2013 0:008/22/2014 0:00
              E2BDAB1B-6893-4ED3-A5F0-07443356ADC1ConsultantInactiveQ10168155 {036C01DD-6D89-4309-A991-C3B0425DD521}11/4/2013 0:008/22/2014 0:00
              A73DC290-6F31-4416-ACC8-6E927F5136DAConsultantInactiveQ10194256 {AC22C137-49DF-4834-A026-46EAA37982A4}6/30/2014 0:008/22/2014 0:00
              880EE92F-977B-4405-A009-3552836CEAF3ConsultantInactiveQ10095589{DD119A0A-DD31-4410-B91C-04519AA4BB4A}1/24/2011 0:008/19/2014 0:00
              24EF6EAE-A161-42CA-A326-7CA1D56E6464EmployeeInactive251055{8DC812B9-FDC4-43D2-A01C-23C869D48F78}5/21/2007 0:008/18/2014 0:00
              84855470-946E-42B1-BDE0-4CF5D75C5D63EmployeeInactive267148{A5A5EA7F-3E71-411D-91FD-2569BBCA2A56}1/10/2013 0:008/18/2014 0:00
              7AA18B56-E30C-4409-9A60-456083B09F88EmployeeInactive250100{927DDB33-3DC5-481F-8F2B-946411FC20A4}11/30/2009 0:008/15/2014 0:00
              3947B79C-21DC-4E29-88BA-3EBD74349D73EmployeeInactive250114{C69D57D2-733E-4BCC-9D0C-296D09A5EB85}5/24/2010 0:008/15/2014 0:00
              32238C78-905C-4CCD-A1C4-1564E3BC9DC2ConsultantInactiveQ10148735{FB9480BF-B416-43C8-9F46-9E01B7779DEC}6/17/2013 0:008/15/2014 0:00
              ED24F357-031A-405B-A595-7E6C1DB6B27AFreelancerInactiveQ10168040{23E0A12C-A4E2-4B23-AF8A-37882F04DBAD}11/1/2013 0:008/15/2014 0:00
              C9A21BF5-A87A-495A-B748-F110350CD4B9InternInactive268636{4F640F73-A400-4510-AF98-D2226B0C2646}5/19/2014 0:008/15/2014 0:00
              57785295-5188-4DF5-BC79-05E565155E31InternInactiveQ10187911{DF783447-63FA-4E95-9A7A-597014C967F8}6/2/2014 0:008/15/2014 0:00
              88A98463-DDE2-4FA9-83E9-8EFA836CB153ConsultantInactiveQ10186023{CD102704-7A95-4621-A813-301944FB6714}4/14/2014 0:008/14/2014 0:00
              598D7C88-5274-4E59-86A9-C393C81E5AF9EmployeeInactive249440{CA4875EE-2719-446E-96B3-669180E3F2E8}10/31/2011 0:008/13/2014 0:00
              0B5CD993-B296-45C4-A912-4022DFBB0E3DEmployeeInactiveQ10195433{5C1F1317-00A2-4533-9A45-1D62A6F6962A}7/21/2014 0:007/21/2014 0:00
              64D81E46-B341-4BA8-AB84-BD3374275994ConsultantInactiveQ10143930{F3EEAAC0-1AD0-47B6-8C2D-EC5B45A61BBF}5/21/2014 0:007/20/2014 0:00
              AC2D14FE-24B0-44E8-AC8B-8FFDD0C92E58EmployeeInactive258317{8B8A3BF7-6D09-4946-89C8-DD846C31BFAF}5/21/2012 0:006/30/2014 0:00
              7BBC8BB2-5CD6-4480-9E37-EB63C15E0C03ConsultantInactiveQ10171611{3EC53958-86AA-4E2A-8D63-A1E7BE13F37A}11/20/2013 0:005/16/2014 0:00
              CB2D013F-0754-4482-AEA5-4869BC74FD05ConsultantInactiveQ10181587{F436E5FF-8E0D-40F7-A9FA-E85538C9355F}2/13/2014 0:005/16/2014 0:00
              2ADC7594-8BCF-4FC0-9C6F-5F6316E83104ConsultantInactiveQ10105147{09DF840A-A4DA-488D-A176-F10F74E2BE38}11/13/2013 0:005/16/2014 0:00
              6532A55C-DEA0-4994-9194-864C86166599EmployeeInactiveQ10185953{E958C9C9-592A-4C98-91BC-AA02158CD3BC}4/21/2014 0:004/21/2014 0:00
              D1A344B7-81DE-40AD-AB7D-0E48F5394265EmployeeInactive248805{88403C3F-A56A-460D-89CF-BAEAC9270200}5/16/2011 0:004/19/2014 0:00
              • Re: Counting Accounts Termed
                Simen Kind Gulbrandsen

                if the accounts go from Active to Inactive, does that not mean that we only want to count accounts that are inactive?

                 

                Add these lines to your load script so you dont have to use Today() in charts and to simplify your expression.

                 

                let vLastMonthStart = Floor(Monthstart(Addmonths(today(),-1)));

                let vLastMonthEnd = Floor(MonthEnd(Addmonths(today(),-1)));

                 

                then try the expression:


                =Count({$<[Date Termed]= {">=$(vLastMonthStart)<=$(vLastMonthEnd)"},

                [Account Status]={'Inactive'}>} DISTINCT
                [OwnerID])