Re: Counting Accounts Termed - Qlik Community

Report Inappropriate Content · ‎2014-09-24

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?

MK_QSL · ‎2014-09-24

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

vgutkovsky · ‎2014-09-24

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

Report Inappropriate Content · ‎2014-09-24

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

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

D1A344B7-81DE-40AD-AB7D-0E48F5394265

Employee

Inactive

248805

{88403C3F-A56A-460D-89CF-BAEAC9270200}

5/16/2011 0:00

4/19/2014 0:00

simenkg · ‎2014-09-24

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])