Count Expression in Chart

[Anonymous]

I need help creating a COUNT expression with the attached data set.

Dimension: StartDate_YYYYMM

Expression:

AssignDate IS NOT NULL

Status = Open OR Status  = Closed when Enddate_YYYYMM = StartDate_YYYYMM

201608 = 50,213

201607 = 52,543

201606 = 45,026

[Anonymous]

So Enddate_YYYYMM = the dimension StartDate_YYYYMM

201608 = AssignDate Is Not Null.  Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201608

201607 =  AssignDate Is Not Null.  Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201607

201607 =  AssignDate Is Not Null.  Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201606

201608 = 50,213

201607 = 52,543

201606 = 45,026

This is counting an accumulation of customers that were active at some point during the dimension month.

[sunny_talwar]

There was close component also, right? Where does that come into picture?

[Anonymous]

Sorry, yes. I have updated my reply

[sunny_talwar]

So basically there are two conditions?

1) Count ID where AssignDate is Not Null and Status = OPEN

2) Count ID where AssignDate is Not Null and Status = CLOSED and Enddate_YYYYMM = StartDate_YYYYMM?

[Anonymous]

Yes, but when I say StartDate = EndDate. I mean for that month. 

So Enddate_YYYYMM = the dimension StartDate_YYYYMM

201608 = AssignDate Is Not Null.  Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201608

201607 =  AssignDate Is Not Null.  Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201607

201607 =  AssignDate Is Not Null.  Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201606

[sunny_talwar]

And which of the two conditions getting accumulated? Both or one of the two?

[Anonymous]

Right. Accumulate Condition one: Count ID where AssignDate is Not Null and Status = OPEN.

Condition two is not accumulated.

[sunny_talwar]

I have gotten closer , but still not there. Would you be able to show in Excel how you reached those numbers for 201608?

[sunny_talwar]

I am getting 49135 for the cumulative open condition and 23 for the non-cumulative closed condition from the Excel file. Which matches with the output for 201608. How are you getting 50,213?

[Anonymous]

Sorry, I'm probably making this more diffcult the need be.

49,135 for condition one is correct.

However, I get an additional 1,078 when I filter on AssignDate is still blank/null. Status = Closed and EndDate = 201608 .

For 201607 I get 52,543

Condition 1: 48,315

Condition 2: 4,227   Enddate = 201607, Status = Closed,AssignDate is not blank/null