Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator II
Creator II

Count ID based on field

I have ID, Date, MonthYear and Status as fields.

1. I have to count all the IDs that have more than one Running or Pending or Complete Status

2. I also have to count IDs that have more than one Running or Pending or Complete Status and fall within one year from the Date.

For problem 1,  I am using Count(DISTINCT {<ID = {"=Count({<Status = {'Running', 'Complete', 'Pending'}>} Status) > 1"}>} ID) but this also counts other ID rows with other Status values.

For problem 2, I am using Count(DISTINCT {<ID = {"=Count({<Status = {'Completed'},MonthYear ={">=$(=MonthName(addmonths(today(),-12)))"}>} Status) > 1"}>} ID) but this does not work either. The Set expression starts treating everything after 'MonthYear' as some kind of variable and gives errors.

I am attaching my QVW with some sample data. I have reached out before but couldn't really find the end solution for these 2 requirements. So reposting for bumping this question up. Any kind of help is highly appreciated.

 

 

1 Solution

Accepted Solutions
yassinemhadhbi
Creator
Creator

Try this: 

=sum(

aggr(count(distinct if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1)),ID)
)

Best Regards
Yassine Mhadhbi

View solution in original post

11 Replies
MayilVahanan

HI 

Try like below

Count(DISTINCT {<Status = {'Running', 'Complete', 'Pending'}>} ID)

Count(DISTINCT {<Status = {'Complete'},Date ={">=$(=MonthStart(addmonths(today(),-12)))"}>}ID)

Thanks & Regards,
Mayil Vahanan R
yassinemhadhbi
Creator
Creator

for problem 1

sum(if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>} ID),ID)>1,1,0))

For problem 2

sum(if(aggr(Count({<Status = {'Complete'},Date ={">=$(=MonthStart(addmonths(today(),-12)))"}>}ID),ID)>1,1,0))

Best Regards
Yassine Mhadhbi
qlikwiz123
Creator II
Creator II
Author

Hi,

1 is failing

qlikwiz123_0-1623767825853.png

 

It is counting even though there is only one Complete and one Running. It should be Count if (Status=Complete or Running or Pending) occurs more than once per SID.

In simple terms, count SID if Count(Status=Running) >1 or Count(Status=Pending)>1 or Count(Status=Complete)>1

yassinemhadhbi
Creator
Creator

Can you please remove the Status from the dimension

Best Regards
Yassine Mhadhbi
qlikwiz123
Creator II
Creator II
Author

Removed. Still see the count for ID A560591

qlikwiz123_0-1623768529160.png

 

yassinemhadhbi
Creator
Creator

Please , try this : 

sum(if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1,0))

 

Best Regards
Yassine Mhadhbi
qlikwiz123
Creator II
Creator II
Author

qlikwiz123_0-1623769627605.png

Still does not work.

The ID is being counted twice here. It should be counted only once if it satisfies the conditon.

yassinemhadhbi
Creator
Creator

count(distinct if(aggr(Count({<Status = {'Running', 'Complete', 'Pending'}>}ID),ID,Status)>1,1))

Just replace the sum with Count

Best Regards
Yassine Mhadhbi
qlikwiz123
Creator II
Creator II
Author

Hi,

This seems to be working in a table with ID as dimension.

How do I show this number in a text box?