10 Replies Latest reply: Oct 12, 2017 11:14 AM by Darrell Tobin

# Sum # of tasks between 2 date ranges

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?

Becs

• ###### Re: Sum # of tasks between 2 date ranges

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

• ###### Re: Sum # of tasks between 2 date ranges

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

• ###### Re: Sum # of tasks between 2 date ranges

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

• ###### Re: Sum # of tasks between 2 date ranges

Yes it does

• ###### Re: Sum # of tasks between 2 date ranges

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?

• ###### Re: Sum # of tasks between 2 date ranges

= 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

• ###### Re: Sum # of tasks between 2 date ranges

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

and

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

and

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

return the expected results.

- Marcus

• ###### Re: Sum # of tasks between 2 date ranges

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

• ###### Re: Sum # of tasks between 2 date ranges

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).

• ###### Re: Sum # of tasks between 2 date ranges

Hi Rebecca,

Dates in set analysis is a nightmare, the syntax is difficult and Qlik sometimes gets confused as to if the field is a date or not.

Two work arounds below:

1. Create a dateid  for each date and use the dateid field in your set analysis instead.  Syntax is easier and it will work
2. Load an excel table that creates scores lookup for each date

e.g.

Date             Score

15/9/17            5

16/09/17          5

01/10/17          10

etc.

All you will have to do then is sum the Score value...