Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III
Creator III

Calculation in Set Analysis

Hi,

This seems like it should be straightforward, but I can't get it to work...

Within my large dataset, each row has a 'CHierarchy' and 'THierarchy' numerical field.

I want to count each case where CHierarchy - THierarchy = 1.

I've tried:

COUNT(CHierarchy-THierarchy=1)

COUNT({<CHierarchy-THierarchy={1}>})

Then in desperation created a new field called 'CaseCounter' (with a value of '1' on each row), and tried:

COUNT({<CHierarchy-THierarchy={1}>} CaseCounter)


But nothing has worked so far.


Any help much appreciated.

1 Solution

Accepted Solutions
Anonymous
Not applicable

You can also add a new field in your script so that you do not need an if statement in your expression.

(CHierarchy - THierarchy) as diffHierarchy

Than do

Count({<diffHierarchy={1}>} CaseCounter)

or

Sum({<diffHierarchy={1}>} 1)

View solution in original post

16 Replies
swuehl
MVP
MVP

Try

COUNT( If( CHierarchy-THierarchy=1, 1, NULL() ))


or


SUM( If( CHierarchy-THierarchy=1, 1))


Or create a field in the script with above if() statement, then count / sum this field.

sunny_talwar

You will need a unique field here to do it using set analysis:

Sum({<UniqueField = {"=(CHierarchy - THierarchy) = 1"}>} 1)

or try using if statement:

Sum(If((CHierarchy - THierarchy) = 1, 1))

Not applicable

try
count (if(CHierarchy-THierarchy=1, 1,0))
Anonymous
Not applicable

You can also add a new field in your script so that you do not need an if statement in your expression.

(CHierarchy - THierarchy) as diffHierarchy

Than do

Count({<diffHierarchy={1}>} CaseCounter)

or

Sum({<diffHierarchy={1}>} 1)

jessica_webb
Creator III
Creator III
Author

I like the idea of creating the new field in script to avoid having too  many if statements.

However, I did try this before but I'm having trouble because the two fields are in different tables.

JOIN [Table MP1]:

[Table Target_Grade]:

LOAD SubjectType,

     Actual_TargetGrade,

     TGradePoints_New,

     TGradePoints_Old,

     THierarchy

FROM

[\\L...\Grade map for QV.xls]

(biff, embedded labels, table is [Target points$]);

JOIN [Table MP1]:

LOAD SubjectType,

     Actual_CurrentGrade,

     CGradePoints_New,

     CGradePoints_Old,

     CHierarchy,

     CHierarchy-Lookup('THierarchy','SubjectType',SubjectType,'Table Target_Grade') as Grade_Difference

FROM

[\\L...\Grade map for QV.xls]

(biff, embedded labels, table is [Current points$]);

The last line of script is clearly where I'm going wrong, but I'm not sure why...

swuehl
MVP
MVP

Are you sure that table 'Table Target_Grade' does exist when you call the Lookup() function? In above script snippet, the first LOAD is JOINed to [Table MP1], so this won't create a table [Table Target_Grade].

jessica_webb
Creator III
Creator III
Author

Ah, ok - that's good to know.

Have changed the Lookup to 'Table MP1', but still doesn't seem to be working. It's not throwing up any errors, but the field I have for 'Grade_Difference' remains blank...

I've worked out why it isn't working at the moment:

CHierarchy and THierarchy are identical within the tables, so CHierarchy-THierarchy in the script, always gives a value of '0'.

However, if I do 'CHierarcy-THierarchy' as an expression, with 'Person ID' as a dimension, it works (as each person will have a different CHierarchy score and THierarchy score). So I need to find a way to calculate this new field in the script for every row in 'Table MP1'.

perumal_41
Partner - Specialist II
Partner - Specialist II

Please use below expression

Sum({<SubjectType= {"=(CHierarchy - THierarchy) = 1"}>} 1)

jessica_webb
Creator III
Creator III
Author

Thanks for all suggestions so far - unfortunately the 'COUNT/SUM( If( CHierarchy-THierarchy=1, 1, NULL() ))' type suggestions aren't working. Each gives me a different total that doesn't match what I'm expecting to see.

I'm sure it's an error I'm making somewhere, but I'm not sure where it is yet. Creating a new field with the grade difference seems like my best bet at the moment, but still not getting anywhere with that either...

Will keep trying and post on here when the correct solution comes up.