Sum # of tasks between 2 date ranges

[#1]

Hi

I need to sum the number of tasks a person created between 2 dates, then assign them points for every 100 tasks created. For tasks created between 15/09/2017 and 30/09/2017 (inclusive) I want to award 5 points for every 100 tasks, and between 01/10/2017 and 15/10/2017 (inclusive) I want to award 10 points for every 100 tasks created.

I assume set analysis is required?

Thanks in advance!

Becs

[marcus_sommer]

Maybe something like this:

rangesum(

floor(sum({< Date = {">='15/09/2017'<='30/09/2017'"}>} Points) / 100) * 5,

floor(sum({< Date = {">='01/10/2017'<='15/10/2017'"}>} Points) / 100) * 10)

- Marcus

[#3]

Thanks Marcus, this returns zero unfortunately. I think because i need to count the tasks?

[marcus_sommer]

Your field is named as "Date" has also this format '15/09/2017' ?

[#5]

Yes it does

[marcus_sommer]

Please post your expression and also check if by your selections the sum of points could be higher as 100 - for checking you could also set the 100 to 1 and 5 respectively 10 to 1 to get the sum of points without the advanced scoring. Also is your sum-field named with Points?

[#7]

= Rangesum  (floor (sum ({< Date = {">= '15/09/2017' <= '30/09/2017'"}>} Task)/100) *5, floor (sum ({< Date = {">= '01/10/2017' <= '15/10/2017'"}>} Task)/100) *10)

The field name is "Task" which is populated with "1" on each line item

[marcus_sommer]

I think that the point to reduce the complexity and therefore possible errors by checking if:

sum (Task)

     and

sum ({< Date = {'15/09/2017'}>} Task)

     and

sum ({< Date = {">='15/09/2017'"}>} Task)

return the expected results.

- Marcus

[#9]

Sum(Task) works but the other 2 expressions don't

[marcus_sommer]

Then is something with Date wrong. Is Date really a Date or is it perhaps a timestamp or even a string? Please provide a screenshot of a listbox with Date and an extra included expression of num(Date).