Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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:

 Date Series_ID Status 1/1/2010 101 Value N/A 2/1/2010 101 Available 3/1/2010 101 Interrupted . 101 . . 101 . . 101 . 4/1/2018 101 Available 5/1/2018 101 Available 1/1/2010 102 Available 2/1/2010 102 Available 3/1/2010 102 Available . 102 Available . 102 Available . 102 Available 4/1/2018 102 Available 5/1/2018 102 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
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
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
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?

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.

Specialist

Hum, no...

Can you share your dataset ?

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

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

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
Specialist

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

Community Browser