# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

## Re: Sum with condition for less than specific dimension value

See attached qvf.

talk is cheap, supply exceeds demand
15 Replies

## Re: Sum with condition for less than specific dimension value

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

talk is cheap, supply exceeds demand
Not applicable

## Re: Sum with condition for less than specific dimension value

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

## Re: Sum with condition for less than specific dimension value

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

talk is cheap, supply exceeds demand
Valued Contributor

## Re: Sum with condition for less than specific dimension value

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

## Re: Sum with condition for less than specific dimension value

still not working...

Not applicable

## Re: Sum with condition for less than specific dimension value

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.

## Re: Sum with condition for less than specific dimension value

See attached qvf.

talk is cheap, supply exceeds demand
Not applicable

## Re: Sum with condition for less than specific dimension value

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

## Re: Sum with condition for less than specific dimension value

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