Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum with condition for less than specific dimension value

Hi experts,

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

My data like below:

ScoreNumOfStudents
2608
2625
2671
2734
2778
2782

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached qvf.


talk is cheap, supply exceeds demand

View solution in original post

15 Replies
Gysbert_Wassenaar

Try sum(aggr(rangesum(above(sum(NumOfStudents),0,rowno()),Score))


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks but not working. "Error in Expression: Nested aggregation not allowed"

Gysbert_Wassenaar

Yeah, my mistake. Try sum(aggr(rangesum(above(sum(NumOfStudents),0,rowno()),Score))


talk is cheap, supply exceeds demand
brunobertels
Master
Master

Hi

Try This

Sum({$<Score={">=$(=min(Score))<=$(Score)"}>}NumOfStudents)

I used it in a KPI Object and seems to work well

See app attached

Not applicable
Author

still not working...

Not applicable
Author

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.

Gysbert_Wassenaar

See attached qvf.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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:

 

IDScoreNumOfStudentsCumulative
126080
126258
1267113
227340
1277814
1278232
228064
1285934

thanks,

Jing