CountIfs

[alwayslearning]

Hi,

I am new to Qlik and so help would be much appreciated. I am trying to perform what in Excel would be CountIFS.  Counting based on multiple Columns.

I want to be able to count nulls for Multiple Columns: e.g.

Group	Forename	Surname	Age
1	John	Smith	51
1	Sam	Shaw	21
2	Harold
2	Amy
3		Jones

You can see in Forename there is 1 Null, Surname has 2 and Age has 3.  I want to be able to count these nulls in various ways.

1. I want to be able to sum the null of multiple columns i.e. Forename + Surname = 3 Nulls

2. I want to be able to sum the null of multiple columns and then base it on multiple groups i.e. Forename + Surname of Group2 = 2 Nulls & Forename + Surname of Group2 +3 = 3 Nulls etc.

I've come across countif through: =count({<Field={'A','B'}>} Field), which I think is a good starting point.

Thank you.

Qlikview Novice

[sunny_talwar]

May be these if they are not white spaces, but true nulls

1) RangeSum(NullCount(Forename), NullCount(Surname))

2) Not sure I understand your second requirement, can you elaborate?

[Digvijay_Singh]

I think the 2nd requirement can be met as below -

The top list boxes can be filtered to understand group wise null for selected fields.

[alwayslearning]

Thanks Digvijay,

The solution provides almost what I need.  The one additional thing I hope you can help with is I don't want a listbox to filter by field or group.

i.e. I just want to sum the nulls of multiple fields and groups and return a value in a text box of which ever multiple groupings I need.  The other thing is I want to combine the sum of multiple groups.  E.g. I want to sum nulls of group 2 & 3 of forename a surname

Thanks

[alwayslearning]

Hi Sunny,

The first response works great.

I've a bit more detail on number 2 below

[sunny_talwar]

I still don't understand what you are looking to do. May be digvijay‌ might be able to help better . But in case he is unable to, please provide more details on what you are looking to get.

Best,

Sunny

[Digvijay_Singh]

Not sure but see if this can help.

Text box 1 - Group wise sum of nulls of all fields, since you don't want list box bases selection, manually you need to remove fields which you want to exclude.

=Sum(Aggr(RangeSum(NullCount(Forename),NullCount(Surname),NullCount(Age)),Group))

Text Box 2  - If you want specific groups and don't want to select groups from list box, then manually you can change groups in set analysis below.

=Sum({<Group={3}>}Aggr(RangeSum(NullCount({<Group={3}>}Forename),NullCount({<Group={3}>}Surname),NullCount({<Group={3}>}Age)),Group))

[MarcoWedel · Accepted solution]

Hi,

maybe this helps:

table1:
LOAD *,
     -IsNull(Forename) as IsNullForename,
     -IsNull(Surname)  as IsNullSurname,
     -IsNull(Age)      as IsNullAge;
LOAD RecNo() as ID,
     Group, 
     If(Len(Forename),Forename) as Forename, 
     If(Len(Surname),Surname)   as Surname, 
     If(Len(Age),Age)           as Age
FROM [https://community.qlik.com/thread/233983] (html, codepage is 1252, embedded labels, table is @1);

table2:
CrossTable (NullField, IsNull)
LOAD ID,
     IsNullForename,
     IsNullSurname,
     IsNullAge
Resident table1;

regards

Marco

[alwayslearning]

Thanks Sunny for the Help

& Thanks Digvijay

Your second part:

=Sum({<Group={3}>}Aggr(RangeSum(NullCount({<Group={3}>}Forename),NullCount({<Group={3}>}Surname),NullCount({<Group={3}>}Age)),Group))

This worked for me, however my actual code is a for projects and looks something like:

=Sum({<[Project ID]={PJ0001}>}Aggr(RangeSum(NullCount({<[Project ID]={PJ0001}>}Name),NullCount({<[Project Id]={PJ0001}>}[Description])),[Project ID]))  - Even though you see I am only checking two fields for Nulls the value coming up showed up as 3?

However I believe Marco has given a different solution which worked for me.  By manipulating the data, making nulls = 1 and nonenull= 0

[alwayslearning]

Hi Marco,  Thanks for this.  This seems to work perfectly for me.

I slightly edited your code for IsNull in the load script, and one line placed Nulls = 1 and none null = 0

if(IsNull(ForeName)=0, ForeName=0, '1') As Nullname