Count Where Field Is Populated or Not

[haymarketpaul]

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

[maxgro · Accepted solution]

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)

[kaushiknsolanki]

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

[MarcoWedel]

Hi,

one solution using a flag could be:

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

hope this helps

regards

Marco

[trdandamudi]

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)

[haymarketpaul]

Thanks Everyone - All your solutions work