Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This is -> Sum(Aggr(Count(DISTINCT [Start Date]), [Client Ref]))
counting distinct Start Date for each Client Ref and then summing it up
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]))
Slight Change:
=Sum(Aggr(If(Len(Trim(Concat([Start Date], ','))) > 1, 1, 0), [Client Ref]))
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.
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]))
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
See if this one helps:
Sum(Aggr(Count(DISTINCT [Start Date]), [Client Ref]))
Sum(Aggr(Count(DISTINCT [End Date]), [Client Ref]))
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
This is -> Sum(Aggr(Count(DISTINCT [Start Date]), [Client Ref]))
counting distinct Start Date for each Client Ref and then summing it up