topic Re: Count Where Field Is Populated or Not in QlikView

Count Where Field Is Populated or Not

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

QV12 SR3

I am trying to count instances where users have a job title or not but i'm not having much luck with my counting.

I've attached my qvw filtered to the example below.

In this example i would expect the Last Page Type of Bulletin to have a With Job Title count of 3 based on the 3 data rows you can see that clearly have job titles. For some reason it's showing the count of 3 in the Without Job Title column

This is what i'm using to count the field when it's populated:

Fieldname: Job_Title_Free_Text_Windpower_Master

=If(Len(Trim(Job_Title_Free_Text_Windpower_Master))>0,Count([Row #]))

and when it's not populated

=If(Len(Trim(Job_Title_Free_Text_Windpower_Master))=0,Count([Row #]))

Not quite sure where i'm going wrong or even why the count of 1 for the Standard Last Page Type is showing correctly and the Bulletin count is not so any pointers much appreciated

Re: Count Where Field Is Populated or Not

maxgro — Sun, 13 Nov 2016 16:30:24 GMT

try with

count({$ <UserID={"=len(trim(Job_Title_Free_Text_Windpower_Master))>0"}>} distinct UserID)

count({$ <UserID={"=len(trim(Job_Title_Free_Text_Windpower_Master))=0"}>} distinct UserID)

Re: Count Where Field Is Populated or Not

kaushiknsolanki — Sun, 13 Nov 2016 16:33:26 GMT

You can also try,

=Sum(Aggr(Count(If(Len(Trim(Job_Title_Free_Text_Windpower_Master))>0,[Row #])),[Row #]))

=Sum(Aggr(Count(If(Len(Trim(Job_Title_Free_Text_Windpower_Master))=0,[Row #])),[Row #]))

Regards,

Kaushik Solanki

Re: Count Where Field Is Populated or Not

MarcoWedel — Mon, 14 Nov 2016 00:20:59 GMT

Hi,

one solution using a flag could be:

Sign(Len(Trim(Job_Title_Free_Text_Windpower_Master))) as JobTitlePresent

hope this helps

regards

Marco

Re: Count Where Field Is Populated or Not

trdandamudi — Mon, 14 Nov 2016 00:46:27 GMT

May be as below:

With_Title:

count({$ <Job_Title={"=len(trim(Job_Title))>0"}>} distinct UserId)

No_Title:

count({$ <Job_Title -={"=len(trim(Job_Title))>0"}>} distinct UserId)

Re: Count Where Field Is Populated or Not

haymarketpaul — Mon, 14 Nov 2016 12:54:48 GMT

Thanks Everyone - All your solutions work