Count records if values in Field1 are greater than values in Field2

Anonymous — Mon, 26 Mar 2018 15:17:20 GMT

Hi All,

a bit of advice on the below would be greatly appreciated please. The data/code looks like this:

Set NullInterpret = '';

Table:

load * inline [

Item,Field1,Field2

A,1,1

B,1,2

C,2,2

D,2,3

E,3,3

F,,3]; //null value in Field1

The goal is to create a pivot with Field1 as dimension and the expression should count all records with Field2 value >= current dimension value of Field1. Field1 may have nulls and these need to be hidden in dimension.

The outcome should be as the first two columns below, I only added explanations in the 3rd column:

Field1	Record Count	explanation: all records with Field2 >= current Field1 dimension value
1	6	all records meet criterium of Field2>=1
2	5	only record A does not meet criterium Field2>=2
3	3	records D, E, F meet criterium Field2>=3

Set analysis + aggr won't work because set is evaluated at chart level and not record level so no point in quoting Field1 in set expression.

I tried As-Of-Table technique but I cannot figure out how to link the two fields together there.

Any help is greatly appreciated. I was looking for threads on similar problems but i could not find anything.

Regards,

Artur

Re: Count records if values in Field1 are greater than values in Field2

sunny_talwar — Mon, 26 Mar 2018 15:44:08 GMT

May be try this

SubStringCount(Mid(Concat(TOTAL Field2, '|', Field2), Index(Concat(TOTAL Field2, '|', Field2), Field1)), '|') + 1

Re: Count records if values in Field1 are greater than values in Field2

MK_QSL — Mon, 26 Mar 2018 15:44:50 GMT

Load below in your script...

Temp:

Load Distinct Field1 as F1 Resident Table;

Now create a straight or pivot table

Dimension

Expression

SUM(Aggr(NODISTINCT IF(Field2 >= F1, 1, 0),Field2,F1))

topic Count records if values in Field1 are greater than values in Field2 in QlikView

Count records if values in Field1 are greater than values in Field2

Re: Count records if values in Field1 are greater than values in Field2

Re: Count records if values in Field1 are greater than values in Field2