topic Re: Calculation in Set Analysis in QlikView

Calculation in Set Analysis

jessica_webb — Fri, 18 Dec 2015 14:18:50 GMT

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.

Re: Calculation in Set Analysis

swuehl — Fri, 18 Dec 2015 16:47:46 GMT

Try

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

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

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

Re: Calculation in Set Analysis

sunny_talwar — Fri, 18 Dec 2015 16:52:46 GMT

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

Re: Calculation in Set Analysis

Fri, 18 Dec 2015 16:54:42 GMT

try

count (if(CHierarchy-THierarchy=1, 1,0))

Re: Calculation in Set Analysis

Anonymous — Fri, 18 Dec 2015 19:20:55 GMT

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)

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

Re: Calculation in Set Analysis

jessica_webb — Mon, 21 Dec 2015 09:48:52 GMT

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

Re: Calculation in Set Analysis

swuehl — Mon, 21 Dec 2015 10:04:14 GMT

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

Re: Calculation in Set Analysis

jessica_webb — Mon, 21 Dec 2015 10:06:43 GMT

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

Re: Calculation in Set Analysis

perumal_41 — Mon, 21 Dec 2015 10:10:28 GMT

Please use below expression

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

Re: Calculation in Set Analysis

jessica_webb — Mon, 21 Dec 2015 10:15:06 GMT

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.

Re: Calculation in Set Analysis

jessica_webb — Mon, 21 Dec 2015 10:16:53 GMT

Thanks Sunny.

I'm not sure I have a unique field... I have a field called 'Person ID' (which is obviously unique for each person), but a person could have 'CHierarchy-THierarchy' totalling '1' on more than one row, so they would need to be counted more than once. Could I maybe create a new field that numbers every row differently?

Re: Calculation in Set Analysis

jessica_webb — Mon, 21 Dec 2015 10:18:18 GMT

Thanks Perumal - unfortunately this doesn't work.

It actually gives me 'No data to display', but I think it wouldn't work anyway as 'SubjectType' could have several instances where 'CHierarchy-THierarchy' totals '1', and I want to know how many instances.

Re: Calculation in Set Analysis

perumal_41 — Mon, 21 Dec 2015 13:22:50 GMT

Hi,

is possible to upload sample application ?

Re: Calculation in Set Analysis

effinty2112 — Mon, 21 Dec 2015 13:44:47 GMT

Hi Jessica,

Try:

=count(Aggr(count(if(CHierarchy - THierarch = 1,1)), RowID))

in this expression replace RowID with whatever field identifies each row uniquely. If no such single row exists but a number of fields together identifyeach row then replace RowID with these field names separated by a comma.

Re: Calculation in Set Analysis

sunny_talwar — Mon, 21 Dec 2015 13:47:24 GMT

May be create a unique id using RowNo() function in the script.

Re: Calculation in Set Analysis

swuehl — Mon, 21 Dec 2015 13:55:46 GMT

It would be much easier to help if you could upload some sample lines of data, best within a reloadable sample QVW (e.g. as INLINE tables or attached excel files). And with a description of your expected results attached.

Re: Calculation in Set Analysis

jessica_webb — Mon, 21 Dec 2015 13:59:43 GMT

Thank you Swuehl - started working on an example file for you (you are right, it would have been easier - I was just hoping this would be a quick fix).

Anyway, it's sorted now with:

LOAD*, CHierarchy-THierarchy as Grade_Difference

Resident [Table MP1];

Drop table [Table MP1];

Thanks very much to everyone for their help!