Count date if greater than zero

[#1]

I have a set of date for example

Client Ref	Case Ref	Start Date	End Date
158	1	10/11/14	14/01/15
158	2	10/11/14	14/01/15
158	3	10/11/14	14/01/15
123	1
123	2
147	1	6/7/14	10/9/15
147	2	6/7/14	10/9/15
147	3	6/7/14	10/9/15
147	4	6/7/14	10/9/15

What I need to do it count the Distinct Client Ref but only if the Start date is Greater than 0

What I have is:

If ([Start Date] > 0, Count (Distinct [Client Ref]))

I have tried using this with my data and it returns with a value of 4 but I know there are thousands as we have about 100,000 clients in our Data base.

Thanks in advance

[sunny_talwar]

Are you doing this in a text box? If yes, then try this:

=Sum(Aggr(If(Len(Concat([Start Date], ',')) > 0, 1, 0), [Client Ref]))

[sunny_talwar]

Slight Change:

=Sum(Aggr(If(Len(Trim(Concat([Start Date], ','))) > 1, 1, 0), [Client Ref]))

[#4]

This Seems to Work but when I apply it to also do the same with the End Date I get the same number as with the start date this would not be possible because all cases have a start but not all have ended so the numbers should be different for start and end.

[sunny_talwar]

See if this helps:

For Start Date

=Sum(Aggr(If(Len(Trim(Concat([Start Date]))) > 0, 1, 0), [Client Ref]))

For End Date

=Sum(Aggr(If(Len(Trim(Concat([End Date]))) > 0, 1, 0), [Client Ref]))

[#6]

This still returns the same results as before.

What I would usually do is just do a count of the Start date or End Date but because the client wants this to be done by Distinct Client Ref I think the best way to do this is with an If statement or possibly with some sort of Set analysis. FYI i am using Qlik Sense

[sunny_talwar]

See if this one helps:

Sum(Aggr(Count(DISTINCT [Start Date]), [Client Ref]))

Sum(Aggr(Count(DISTINCT [End Date]), [Client Ref]))

[#8]

This will only Count Distinct Date so If I have multiple Client Refs on the same day it will only count them all as One.

Could it be done like

Sum(Aggr(Count [Start Date]), Distinct [Client Ref]))

Sum(Aggr(Count [End Date]), Distinct [Client Ref]))

Cheers

[sunny_talwar · Accepted solution]

This is -> Sum(Aggr(Count(DISTINCT [Start Date]), [Client Ref]))

counting distinct Start Date for each Client Ref and then summing it up