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

[Anonymous]

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

[sunny_talwar]

May be try this

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

[MK_QSL]

Load below in your script...

Temp:

Load Distinct Field1 as F1 Resident Table;

Now create a straight or pivot table

Dimension

F1

Expression

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