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
