topic Re: Count date if greater than zero in App Development

Count date if greater than zero

Tue, 15 Sep 2015 14:37:19 GMT

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

Re: Count date if greater than zero

sunny_talwar — Tue, 15 Sep 2015 15:05:56 GMT

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

sunny_talwar — Tue, 15 Sep 2015 15:11:00 GMT

Slight Change:

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

Re: Count date if greater than zero

Tue, 15 Sep 2015 15:16:27 GMT

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

sunny_talwar — Tue, 15 Sep 2015 15:22:22 GMT

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

Tue, 15 Sep 2015 15:30:23 GMT

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

sunny_talwar — Tue, 15 Sep 2015 15:34:09 GMT

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

Tue, 15 Sep 2015 15:37:56 GMT

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

sunny_talwar — Tue, 15 Sep 2015 17:04:39 GMT

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

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