Set Analysis Date Range

[jlampard40]

Hi All - I have the current set analysis:

=Count({<[HR Next Appraisal Date] = {'>=$(=Date(Today(1)+3<=$(=Date(Today(1) + 10))'}>} [HR Assignment Number])

I'm trying to get it to show me all records where employee appraisals are due, between 3 days and 10 days from today's date.  The above is pulling records much older than today's date (a year ago!) so bit confused!!!  Can you help?  Many thanks.

[chinnuchinni]

Hi James,

try with below exp :

=Count({<[HR Next Appraisal Date] = {">=$(=date(today()+3)) <=$(=date(today()+10)) "}>}[HR Assignment Number])

[sunny_talwar]

What is the date format for your field HR Next Appraisal Date? Check to make sure that Date() function gives the same format or not. You can do this by using =Date(Today(1) + 3) in a text box object. If it is the same format, then this should work

=Count({<[HR Next Appraisal Date] = {">=$(=Date(Today(1)+3))<=$(=Date(Today(1) + 10))"}>} [HR Assignment Number])

If the format is a little different... for example HR Next Appraisal Date is in format DD/MM/YYYY, but Date() function gives you DD-MM-YYYY... then you need to try this

=Count({<[HR Next Appraisal Date] = {">=$(=Date(Today(1)+3, 'DD/MM/YYYY'))<=$(=Date(Today(1) + 10, 'DD/MM/YYYY'))"}>} [HR Assignment Number])

[jlampard40]

That's great - if I want to put this into an email alert within Qlikview, I can use this formula for the count:

='The following ' & Count({<[HR Next Appraisal Date] = {">=$(=date(today()+3)) <=$(=date(today()+20)) "}>}[HR Assignment Number])

& ' members of staff have their appraisal due within the next week:'

However, how can I then list the staff members - do I use the concat statement?  The fields I'd pull are [HR First Name] and [HR Last Name].  I'm not sure how I can combine the above formula and a concat statement to produce something like this:

'The following 4 members of staff have their appraisal due within the next week:  John Smith and Mike Jones'

Appreciate your help guys.  Thanks.

[sunny_talwar · Accepted solution]

Should be just this

Concat({<[HR Next Appraisal Date] = {">=$(=date(today()+3)) <=$(=date(today()+20)) "}>} [HR First Name] & ' ' & [HR Last Name], ', ')

[jlampard40]

Perfect!!  Was getting so muddled with it - you're a star.  Awesome.  Thanks so much.

[jlampard40]

Quick one Sunny - how would I change the expression to list the names one below the other, rather than as a list with commas i.e.

John Smith

Mike Welsh

Paul Knight

Rgds

[sunny_talwar]

Use this

Concat({<[HR Next Appraisal Date] = {">=$(=date(today()+3)) <=$(=date(today()+20)) "}>} [HR First Name] & ' ' & [HR Last Name], Chr(10))

[jlampard40]

OK - thanks Sunny.  One last one - if I want to add the actual date of the next appraisal in brackets, next to their name - how would I do this.  The field would need to be [HR Next Appraisal Date] - something like this:

John Smith (14/10/2018)

Mike Welsh (15/10/2018)

Paul Knight (16/10/2018)

Rgds

[sunny_talwar]

I guess the same thing we did for First and Last Name

Concat({<[HR Next Appraisal Date] = {">=$(=date(today()+3)) <=$(=date(today()+20)) "}>} [HR First Name] & ' ' & [HR Last Name] & '(' & [HR Next Appraisal Date] & ')', Chr(10))