8 Replies Latest reply: Sep 15, 2015 1:04 PM by Sunny Talwar

# Count date if greater than zero

I have a set of date for example

Client RefCase RefStart DateEnd Date
158110/11/1414/01/15
158210/11/1414/01/15
158310/11/1414/01/15
1231
1232
14716/7/1410/9/15
14726/7/1410/9/15
14736/7/1410/9/15
14746/7/1410/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.

• ###### Re: Count date if greater than zero

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

• ###### Re: Count date if greater than zero

Slight Change:

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

• ###### Re: Count date if greater than zero

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.

• ###### Re: Count date if greater than zero

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

• ###### Re: Count date if greater than zero

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

• ###### Re: Count date if greater than zero

See if this one helps:

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

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

• ###### Re: Count date if greater than zero

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

• ###### Re: Count date if greater than zero

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

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