Calculation in Set Analysis

[jessica_webb]

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.

[jessica_webb]

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?

[jessica_webb]

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.

[perumal_41]

Hi,

is possible to upload sample application ?

[effinty2112]

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.

[sunny_talwar]

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

[swuehl]

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.

[jessica_webb]

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!