Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Contributor III
Contributor III

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
Highlighted
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.

Highlighted

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

Highlighted
Not applicable

try
count (if(CHierarchy-THierarchy=1, 1,0))
Highlighted
Contributor III
Contributor III

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

Highlighted
Creator III
Creator III

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...

Highlighted
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].

Highlighted
Creator III
Creator III

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'.

Highlighted
Partner
Partner

Please use below expression

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

Highlighted
Creator III
Creator III

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.