Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
saurabhk18
Contributor II
Contributor II

Calculate distinct count of field based on value in another field

Hi,

I have a data set with 3 important fields: Date (MM/DD/YYYY), Series_ID and Status. Here is a snapshot:

   

DateSeries_IDStatus
1/1/2010101Value N/A
2/1/2010101Available
3/1/2010101Interrupted
.101.
.101.
.101.
4/1/2018101Available
5/1/2018101Available
1/1/2010102Available
2/1/2010102Available
3/1/2010102Available
.102Available
.102Available
.102Available
4/1/2018102Available
5/1/2018102

Available

From Jan 2010 till date, we have several series IDs. A series ID can have three possible statuses: Value N/A, Available and Interrupted. I wish to show the count of those series IDs which have always had the status 'Available' for a selected time period.

So, in the above case, for the period 2010-2018, only series ID 102 had the value 'Available' every month, and hence the count is 1.

I've tried my luck with the aggr() function. I also created a new column in the data, setting it to 0 whenever the status is Available. Then I tried calculating the sum for every series ID - if the sum is 0, it was always Available. But there is something wrong with my logic.

I'll really appreciate your help. Thanks!

-Saurabh

8 Replies
kfoudhaily
Partner - Creator III
Partner - Creator III

=Count({$<Status={'Available'}>}distinct(Series_ID))

should you limit the dates where to look too or your calender is already limited??

QlikView Qlik Sense consultant
christophebrault
Specialist
Specialist

Hi,

Can you try something like :

Count(Distinct

     Aggr(

          if(Only(Status)='Available',Series_ID)

     ,Series_ID)

)

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
saurabhk18
Contributor II
Contributor II
Author

Hi Christophe. For my data set, the correct value is 809. I'm getting 808 using your expression. Any idea where that difference of 1 is coming from?

saurabhk18
Contributor II
Contributor II
Author

Hi. The user can change the period using a filter. I tried your expression, and it looks like it counts entries with the status 'Available', not distinct series IDs.

christophebrault
Specialist
Specialist

Hum, no...

Can you share your dataset ?

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
saurabhk18
Contributor II
Contributor II
Author

Sorry, I can't. The information is confidential. That's why I created a dummy data set.

christophebrault
Specialist
Specialist

Can't you share your real data but in the form of your sample? With only ID there is no chance your industry or company can be identified...

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
arvind1494
Specialist
Specialist

count({<Status={'Available'}>}distinct Series_ID)