Sum with condition for less than specific dimension value

[#1]

Hi experts,

I believe this should be easy, but I cannot get it correct

My data like below:

Score	NumOfStudents
260	8
262	5
267	1
273	4
277	8
278	2

I would like to sum the NumOfStudents for the score less than the specific score. ex. for score 273, it should sum up NumOfStudents for score 267, 262, 260 which is 8+ 5 +1 = 14. Score is the dimension.

I have tried:

sum([$<Score= {">=$(=Min(Score)) < $(=(Score))"}>} NumOfStudents)  and

sum({$<Score= {">=$(=Min(Score))"}>} NumOfStudents)

but none of them are working.

How should I achieve this? The solution either in expression or data load script will be fine with me .

Thanks,

Jing

[Gysbert_Wassenaar]

Try this:

Temp:

LOAD

     ID,

     Score,

     NumOfStudents

FROM

     ...source...

     ;

Result

LOAD

     ID,

     Score,

     NumOfStudents,

     If(ID<>Previous(ID), NumOfStudents, rangesum(NumOfStudents,peek('Cumulative'))) as Cumulative

RESIDENT

     Temp

ORDER BY ID, Score   

     ;

DROP TABLE Temp;

[#3]

Gysbert, it is not working properly...

Please see my attachment.

[brunobertels]

Hi

Don't know how to handle your requierment in the script but in a table try this expression

max(aggr(rangesum(above(sum({<Score>}NumOfStudents),0,Rowno())),ID,Score)) - NumOfStudents

[#5]

Thanks Bruno. It works in the table and I learned more. However I do need this cumulative data stored in data model for further computing purpose.

[Gysbert_Wassenaar]

Change ID=Previous(ID) to ID<>Previous(ID)

[#7]

Thank you very much Gysber! It works perfectly now. I just wondering why we have to use <> instead of = which makes such a big difference.

I have attached the final qvf for others reference.

And here is the script in case people don't use Sense:

Temp:

LOAD

     ID,

     Score,

     NumOfStudents

FROM

     source;

Result:

LOAD

     ID,

     Score,

     NumOfStudents,

     If(ID<>Previous(ID), NumOfStudents, rangesum(NumOfStudents,peek('Cumulative'))) as Cumulative

RESIDENT

     Temp

ORDER BY ID, Score   

     ;

DROP TABLE Temp;