topic Re: Set Analysis Date Range in QlikView

Set Analysis Date Range

jlampard40 — Fri, 12 Oct 2018 10:01:45 GMT

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.

Re: Set Analysis Date Range

chinnuchinni — Fri, 12 Oct 2018 10:21:25 GMT

Hi James,

try with below exp :

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

Re: Set Analysis Date Range

sunny_talwar — Fri, 12 Oct 2018 10:49:13 GMT

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

Re: Set Analysis Date Range

jlampard40 — Fri, 12 Oct 2018 10:56:15 GMT

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.

Re: Set Analysis Date Range

sunny_talwar — Fri, 12 Oct 2018 10:58:11 GMT

Should be just this

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

Re: Set Analysis Date Range

jlampard40 — Fri, 12 Oct 2018 11:16:32 GMT

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

Re: Set Analysis Date Range

jlampard40 — Fri, 12 Oct 2018 11:36:11 GMT

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

Re: Set Analysis Date Range

sunny_talwar — Fri, 12 Oct 2018 11:37:27 GMT

Use this

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

Re: Set Analysis Date Range

jlampard40 — Fri, 12 Oct 2018 11:43:52 GMT

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

Re: Set Analysis Date Range

sunny_talwar — Fri, 12 Oct 2018 11:45:14 GMT

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

Re: Set Analysis Date Range

jlampard40 — Fri, 12 Oct 2018 11:50:16 GMT

Great - the chr(10) doesn't seem to want to work though Sunny. Still getting the names in one line. Any ideas? Rgds

Re: Set Analysis Date Range

sunny_talwar — Fri, 12 Oct 2018 11:51:39 GMT

Try Chr(13)

Re: Set Analysis Date Range

jlampard40 — Fri, 12 Oct 2018 12:05:51 GMT

No - still no luck Sunny.

Re: Set Analysis Date Range

sunny_talwar — Fri, 12 Oct 2018 12:15:29 GMT

Where exactly are you using this? In a text box object? Would you be able to share an image of the text box object? What delimiter does it show when you use Chr(10) or Chr(13)?