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 sum(aggr(rangesum(above(sum(NumOfStudents),0,rowno()),Score))
Thanks but not working. "Error in Expression: Nested aggregation not allowed"
Yeah, my mistake. Try sum(aggr(rangesum(above(sum(NumOfStudents),0,rowno()),Score))
Hi
Try This
Sum({$<Score={">=$(=min(Score))<=$(Score)"}>}NumOfStudents)
I used it in a KPI Object and seems to work well
See app attached
still not working...
Thanks Bruno. It works for KPI object when you select the score, but when I put in the table, it doesn't work. See my attached qvf. here is the screen shot and I need it in the table.
See attached qvf.
Thanks Gysbert. It is basically working. The only thing is I need to exclude the student# from current score. So I have use this:
max(aggr(rangesum(above(sum({<Score>}NumOfStudents),0,Rowno())),Score)) - NumOfStudents
Gysbert,
Do you think if we can do this kind of calculations in the load script? I know in SQL, we can use subquery.
My real data is actually more complicated with some other conditions and it seems better to save the cumulative data in a separate field.
Or how can I add condition for only sum NumOfStudents for the same ID like data below:
ID | Score | NumOfStudents | Cumulative |
1 | 260 | 8 | 0 |
1 | 262 | 5 | 8 |
1 | 267 | 1 | 13 |
2 | 273 | 4 | 0 |
1 | 277 | 8 | 14 |
1 | 278 | 2 | 32 |
2 | 280 | 6 | 4 |
1 | 285 | 9 | 34 |
thanks,
Jing