Calculate distinct count of field based on value in another field

[saurabhk18]

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

[kfoudhaily]

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

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

[christophebrault]

Hi,

Can you try something like :

Count(Distinct

     Aggr(

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

     ,Series_ID)

)

[saurabhk18]

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]

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]

Hum, no...

Can you share your dataset ?

[saurabhk18]

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

[christophebrault]

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...

[arvind1494]

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