Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Gysbert, it is not working properly...
Please see my attachment.
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
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.
Change ID=Previous(ID) to ID<>Previous(ID)
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;