topic Re: Data to show in between to dates in QlikView

Data to show in between to dates

Fri, 10 Apr 2015 16:03:49 GMT

Hello Everybody,

I need to show ‘Date of visit’ >= ‘Refresh Date’ AND ‘Date of visit’ < ‘Refresh Date + 90 days’

for this I have written the code as

date

([Date of Visit],'dd/MM/yyyy')>= date(ReloadTime()) and [Date of Visit] < Month(AddMonths(date(ReloadTime()),+3));

But it's not showing any data.

Could any one please help.

Urgent.

Thanks in Advanse

Ashish

Message was edited by: ashish das

Re: Data to show in between to dates

MK_QSL — Fri, 10 Apr 2015 16:18:24 GMT

ReloadTime() is giving you TimeStamp so you need to use something like below

Date(Floor(ReloadTime()))

Date(Floor(ReloadTime()),'DD/MM/YYYY')

Also, use DD/MM/YYYY instead of dd/MM/yyyy.

Re: Data to show in between to dates

Fri, 10 Apr 2015 16:32:40 GMT

Hi Manish,

Thanks for your reply.

Using thebelow code also am getting Null.

date

([Date of Visit],'dd/mm/yyyy')>= date(Floor(ReloadTime()),'dd/mm/yyyy') and ([Date of Visit]) < Month(AddMonths(date(Floor(ReloadTime()),'dd/mm/yyyy'),+3))

where as using

month

(AddMonths(Date(Floor(ReloadTime()),'DD/MM/YYYY'),+3))

am getting the value as Jul which is correct.

Could you please tell me whats wrong in my code.

Many Thanks

Ashish

Re: Data to show in between to dates

MK_QSL — Fri, 10 Apr 2015 16:35:59 GMT

Provide Sample data if possible..

Re: Data to show in between to dates

Fri, 10 Apr 2015 16:44:00 GMT

Hi Manish,

Please find the attached sample file.

Thanks

Re: Data to show in between to dates

MK_QSL — Fri, 10 Apr 2015 16:58:32 GMT

What you want as end result?

The aggregation functions missing here...

i.e. SUM, AVG, Min, Max ...

Re: Data to show in between to dates

Fri, 10 Apr 2015 18:43:31 GMT

Hi Manish,

I just want to filter out the data with the ‘Date of visit’ >= ‘Refresh Date’ AND ‘Date of visit’ < ‘Refresh Date + 90 days’.

In script I put the below where condition and loaded it. But it was giving me zero records.

date([Date of Visit],'dd/mm/yyyy')>= date(Floor(ReloadTime()),'dd/mm/yyyy') and ([Date of Visit]) < Month(AddMonths(date(Floor(ReloadTime()),'dd/mm/yyyy'),+3))

Re: Data to show in between to dates

Anonymous — Fri, 10 Apr 2015 20:29:41 GMT

hi,

Re: Data to show in between to dates

MK_QSL — Sat, 11 Apr 2015 07:24:09 GMT

IF(

Date(Floor(Timestamp#([Date of Visit],'YYYY-MM-DD hh:mm:ss'))) >= Date(Floor(ReloadTime())) and

Date(Floor(Timestamp#([Date of Visit],'YYYY-MM-DD hh:mm:ss'))) < Date(Floor(TimeStamp(AddMonths(ReloadTime(),3)))),

Date(Floor(Timestamp#([Date of Visit],'YYYY-MM-DD hh:mm:ss'))))