Qlik Community

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
brunobertels
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:

 

IDScoreNumOfStudentsCumulative
126080
126258
1267113
227340
1277814
1278232
228064
1285934

thanks,

Jing

Community Browser