Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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?

Thanks in advance!

Becs

10 Replies

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

Not applicable

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' ?

Not applicable

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?

Not applicable

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:

sum (Task)

     and

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

     and

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

return the expected results.

- Marcus

Not applicable

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

Community Browser