Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
haymarketpaul
Contributor III

Count Where Field Is Populated or Not

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 #]))

Job Title.png

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Count Where Field Is Populated or Not

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)

View solution in original post

5 Replies
Highlighted
MVP
MVP

Re: Count Where Field Is Populated or Not

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)

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

Re: Count Where Field Is Populated or Not

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted

Re: Count Where Field Is Populated or Not

Hi,

one solution using a flag could be:

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

QlikCommunity_Thread_239642_Pic1.JPG

QlikCommunity_Thread_239642_Pic2.JPG

QlikCommunity_Thread_239642_Pic3.JPG

hope this helps

regards

Marco

Highlighted
trdandamudi
Honored Contributor II

Re: Count Where Field Is Populated or Not

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)

Highlighted
haymarketpaul
Contributor III

Re: Count Where Field Is Populated or Not

Thanks Everyone - All your solutions work