Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
It would be better if you can provide some sample data...
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
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.
DirectoryUserID | Full Name | Resource Manager | Organization | Supervisor | Department Name | Resource Type | Resource Type Description | Company Code | Working Status | Account Status | Identification Number | Director | Location | Job Family | Practice | Technology | EnterpriseMemberID | Date Hired | Date Termed |
F35BEC77-D6A8-4B82-A54E-896C0CF63DF7 | Employee | Inactive | 248620 | {DF9C4265-3359-45D8-BB7B-21D625E022AB} | 5/2/2011 0:00 | 9/15/2014 0:00 | |||||||||||||
25DD61B8-51B5-4CEA-9E2F-193041F9D8AB | Consultant | Inactive | Q10191716 | {F021CDE3-6A26-46E4-82B5-451CE67F9FFB} | 6/16/2014 0:00 | 9/15/2014 0:00 | |||||||||||||
BFAD51EA-AF1B-440C-B8A3-1C432882A7AE | Freelancer | Inactive | Q10160359 | {9823A362-3D95-41AC-8FED-57BD64A89851} | 9/16/2013 0:00 | 8/31/2014 0:00 | |||||||||||||
045DC6F9-14BD-491C-8381-8A572129D577 | Employee | Inactive | 249622 | {6BDF5AD2-B5C0-4328-B5E7-562F2D799D15} | 6/13/2005 0:00 | 8/29/2014 0:00 | |||||||||||||
E5DA4730-478E-4270-8808-6DC78036A254 | Consultant | Inactive | Q10095808 | {F8E2AF8A-ED34-4FF1-BAA9-4771BCFF4C7E} | 6/2/2010 0:00 | 8/29/2014 0:00 | |||||||||||||
A8FD1220-9750-41F6-B3CE-986D422CF793 | Employee | Inactive | 251005 | {D34565A5-57B4-4D7C-B445-217C969B197A} | 10/25/2010 0:00 | 8/29/2014 0:00 | |||||||||||||
D364F80E-15E9-44C9-A2DB-FC45CF640826 | Employee | Inactive | 249123 | {FA17C50F-82A1-4D6D-A771-93F8C6C053BD} | 8/15/2011 0:00 | 8/29/2014 0:00 | |||||||||||||
010DCDD4-9A5A-43D9-9D35-3C3C506EFBD2 | Employee | Inactive | 250995 | {D571C14E-0CC0-46A8-ADD2-FFC96EC2A0CA} | 9/6/2011 0:00 | 8/29/2014 0:00 | |||||||||||||
BE938100-8F94-47E2-A7CC-EF05DFD7464C | Consultant | Inactive | Q10187675 | {51D1D881-CFE4-4EAC-95E6-FD46043FA5DC} | 12/12/2011 0:00 | 8/29/2014 0:00 | |||||||||||||
15703C55-21C1-4995-9E75-B1441793CB74 | Employee | Inactive | 261378 | {2995FCCC-B8ED-4EC4-B93E-9BBA4B43B748} | 9/4/2012 0:00 | 8/29/2014 0:00 | |||||||||||||
0186177B-780A-4079-ADA8-89F6D1B9FECA | Consultant | Inactive | Q10142861 | {06B7D147-0DEC-4906-9FB9-F81D7DCC5722} | 11/5/2012 0:00 | 8/29/2014 0:00 | |||||||||||||
D0495432-FDC7-4301-9705-D4A56356FE7A | Consultant | Inactive | Q10145233 | {2BEEC3AF-8C13-4FF5-94C0-6CD96077CC5E} | 4/10/2013 0:00 | 8/29/2014 0:00 | |||||||||||||
C51F93FD-4CBD-4A8E-ABE3-C04448F53070 | Freelancer | Inactive | Q10159226 | {B86A7EBE-B9B0-480F-B260-A461569F2A3A} | 9/9/2013 0:00 | 8/29/2014 0:00 | |||||||||||||
CF1D47DD-B715-45FD-BA68-9BAB9F774B71 | Freelancer | Inactive | Q10160234 | {2463AD9E-75D0-4F6A-971D-DE47B7E90775} | 11/13/2013 0:00 | 8/29/2014 0:00 | |||||||||||||
F81A26E4-4521-441C-9B89-D25B8245C44D | Freelancer | Inactive | Q10186075 | {6932BAE4-1CAE-41BF-853B-D1CE7BD3A219} | 4/14/2014 0:00 | 8/29/2014 0:00 | |||||||||||||
CAAE5D49-52D1-4616-B03C-A9184BF8CA58 | Consultant | Inactive | Q10185602 | {785E7999-F115-4A5A-9A1A-966B760FA100} | 4/16/2014 0:00 | 8/29/2014 0:00 | |||||||||||||
A6DEA9D8-E8D1-4E26-B04B-26FAF79B9F4F | Consultant | Inactive | Q10187977 | {1C5AAF09-80B9-4AE1-B933-09FD75654906} | 5/12/2014 0:00 | 8/29/2014 0:00 | |||||||||||||
B943B69B-D465-4F6F-BC49-7AB0FEFD3831 | Consultant | Inactive | Q10189188 | {1EA2E303-5FCD-4730-AB65-EFF04AADD1B4} | 5/21/2014 0:00 | 8/29/2014 0:00 | |||||||||||||
BEB5CCC0-82EF-4A60-837F-D67DAE89B419 | Consultant | Inactive | Q10191161 | {B3E07373-6F1E-4B7C-B83A-3CD763AE3685} | 6/9/2014 0:00 | 8/29/2014 0:00 | |||||||||||||
0CD764F8-1A42-40B5-9DCE-2C38F2FE5E9E | Employee | Inactive | 251242 | {6748C666-7039-42E4-A328-6BEC66D5543E} | 9/15/2008 0:00 | 8/26/2014 0:00 | |||||||||||||
6AD26583-F993-4F22-BC1F-870CA32DE38B | Employee | Inactive | 253061 | {C60C93B2-147F-427B-9379-1A7E58FFF429} | 11/3/2013 0:00 | 8/26/2014 0:00 | |||||||||||||
8D9AB515-7A0B-472C-8CDE-F50F40D31C97 | Consultant | Inactive | Q10146320 | {6A582205-E440-48D6-820F-762B9DB4DC31} | 5/8/2013 0:00 | 8/22/2014 0:00 | |||||||||||||
A53A128A-50A0-45B1-9E71-4DD366F001BD | Employee | Inactive | 273108 | {40D1956A-064A-4536-ACB0-DF9BB3DE4572} | 6/11/2013 0:00 | 8/22/2014 0:00 | |||||||||||||
4F9F47DA-9479-4E5A-A7B9-9330B76A5C93 | Employee | Inactive | 269715 | {6872EAF3-2C54-4436-BF4C-7F644C5A9DE9} | 7/15/2013 0:00 | 8/22/2014 0:00 | |||||||||||||
E2BDAB1B-6893-4ED3-A5F0-07443356ADC1 | Consultant | Inactive | Q10168155 | {036C01DD-6D89-4309-A991-C3B0425DD521} | 11/4/2013 0:00 | 8/22/2014 0:00 | |||||||||||||
A73DC290-6F31-4416-ACC8-6E927F5136DA | Consultant | Inactive | Q10194256 | {AC22C137-49DF-4834-A026-46EAA37982A4} | 6/30/2014 0:00 | 8/22/2014 0:00 | |||||||||||||
880EE92F-977B-4405-A009-3552836CEAF3 | Consultant | Inactive | Q10095589 | {DD119A0A-DD31-4410-B91C-04519AA4BB4A} | 1/24/2011 0:00 | 8/19/2014 0:00 | |||||||||||||
24EF6EAE-A161-42CA-A326-7CA1D56E6464 | Employee | Inactive | 251055 | {8DC812B9-FDC4-43D2-A01C-23C869D48F78} | 5/21/2007 0:00 | 8/18/2014 0:00 | |||||||||||||
84855470-946E-42B1-BDE0-4CF5D75C5D63 | Employee | Inactive | 267148 | {A5A5EA7F-3E71-411D-91FD-2569BBCA2A56} | 1/10/2013 0:00 | 8/18/2014 0:00 | |||||||||||||
7AA18B56-E30C-4409-9A60-456083B09F88 | Employee | Inactive | 250100 | {927DDB33-3DC5-481F-8F2B-946411FC20A4} | 11/30/2009 0:00 | 8/15/2014 0:00 | |||||||||||||
3947B79C-21DC-4E29-88BA-3EBD74349D73 | Employee | Inactive | 250114 | {C69D57D2-733E-4BCC-9D0C-296D09A5EB85} | 5/24/2010 0:00 | 8/15/2014 0:00 | |||||||||||||
32238C78-905C-4CCD-A1C4-1564E3BC9DC2 | Consultant | Inactive | Q10148735 | {FB9480BF-B416-43C8-9F46-9E01B7779DEC} | 6/17/2013 0:00 | 8/15/2014 0:00 | |||||||||||||
ED24F357-031A-405B-A595-7E6C1DB6B27A | Freelancer | Inactive | Q10168040 | {23E0A12C-A4E2-4B23-AF8A-37882F04DBAD} | 11/1/2013 0:00 | 8/15/2014 0:00 | |||||||||||||
C9A21BF5-A87A-495A-B748-F110350CD4B9 | Intern | Inactive | 268636 | {4F640F73-A400-4510-AF98-D2226B0C2646} | 5/19/2014 0:00 | 8/15/2014 0:00 | |||||||||||||
57785295-5188-4DF5-BC79-05E565155E31 | Intern | Inactive | Q10187911 | {DF783447-63FA-4E95-9A7A-597014C967F8} | 6/2/2014 0:00 | 8/15/2014 0:00 | |||||||||||||
88A98463-DDE2-4FA9-83E9-8EFA836CB153 | Consultant | Inactive | Q10186023 | {CD102704-7A95-4621-A813-301944FB6714} | 4/14/2014 0:00 | 8/14/2014 0:00 | |||||||||||||
598D7C88-5274-4E59-86A9-C393C81E5AF9 | Employee | Inactive | 249440 | {CA4875EE-2719-446E-96B3-669180E3F2E8} | 10/31/2011 0:00 | 8/13/2014 0:00 | |||||||||||||
0B5CD993-B296-45C4-A912-4022DFBB0E3D | Employee | Inactive | Q10195433 | {5C1F1317-00A2-4533-9A45-1D62A6F6962A} | 7/21/2014 0:00 | 7/21/2014 0:00 | |||||||||||||
64D81E46-B341-4BA8-AB84-BD3374275994 | Consultant | Inactive | Q10143930 | {F3EEAAC0-1AD0-47B6-8C2D-EC5B45A61BBF} | 5/21/2014 0:00 | 7/20/2014 0:00 | |||||||||||||
AC2D14FE-24B0-44E8-AC8B-8FFDD0C92E58 | Employee | Inactive | 258317 | {8B8A3BF7-6D09-4946-89C8-DD846C31BFAF} | 5/21/2012 0:00 | 6/30/2014 0:00 | |||||||||||||
7BBC8BB2-5CD6-4480-9E37-EB63C15E0C03 | Consultant | Inactive | Q10171611 | {3EC53958-86AA-4E2A-8D63-A1E7BE13F37A} | 11/20/2013 0:00 | 5/16/2014 0:00 | |||||||||||||
CB2D013F-0754-4482-AEA5-4869BC74FD05 | Consultant | Inactive | Q10181587 | {F436E5FF-8E0D-40F7-A9FA-E85538C9355F} | 2/13/2014 0:00 | 5/16/2014 0:00 | |||||||||||||
2ADC7594-8BCF-4FC0-9C6F-5F6316E83104 | Consultant | Inactive | Q10105147 | {09DF840A-A4DA-488D-A176-F10F74E2BE38} | 11/13/2013 0:00 | 5/16/2014 0:00 | |||||||||||||
6532A55C-DEA0-4994-9194-864C86166599 | Employee | Inactive | Q10185953 | {E958C9C9-592A-4C98-91BC-AA02158CD3BC} | 4/21/2014 0:00 | 4/21/2014 0:00 | |||||||||||||
D1A344B7-81DE-40AD-AB7D-0E48F5394265 | Employee | Inactive | 248805 | {88403C3F-A56A-460D-89CF-BAEAC9270200} | 5/16/2011 0:00 | 4/19/2014 0:00 |
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])