15 Replies Latest reply: Apr 1, 2016 1:54 PM by Jing Yang

# 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

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

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

• ###### 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))

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

still not working...

• ###### 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

• ###### 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.

• ###### 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

• ###### 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

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

Try this:

Temp:

ID,

Score,

NumOfStudents

FROM

...source...

;

Result

ID,

Score,

NumOfStudents,

If(ID<>Previous(ID), NumOfStudents, rangesum(NumOfStudents,peek('Cumulative'))) as Cumulative

RESIDENT

Temp

ORDER BY ID, Score

;

DROP TABLE Temp;

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

Gysbert, it is not working properly...

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

Change ID=Previous(ID) to ID<>Previous(ID)

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

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:

ID,

Score,

NumOfStudents

FROM

source;

Result:

ID,

Score,

NumOfStudents,

If(ID<>Previous(ID), NumOfStudents, rangesum(NumOfStudents,peek('Cumulative'))) as Cumulative

RESIDENT

Temp

ORDER BY ID, Score

;

DROP TABLE Temp;

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

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

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

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.