topic Re: Re: Date gap in QlikView

Date gap

upaliwije — Sun, 14 Sep 2014 15:33:13 GMT

I want to create a variable to find the no of days between two dates

Date field in my Table is From_Date

I want to create a date as 31-aug-2014 and

then find difference between

31-Aug-2014-from_date

Pls help me to create variable for this

Re: Date gap

maxgro — Sun, 14 Sep 2014 15:41:59 GMT

From_Date - makedate(2014,8,31)

Re: Date gap

anbu1984 — Sun, 14 Sep 2014 15:46:49 GMT

How do you select particular From_Date?

Try like this

Load Max(From_Date) As Max_Dt From Table;

Let Dt = peek('Max_Dt') - Date#('8/31/2014','M/D/YYYY');

TRACE $(Dt);

Re: Date gap

upaliwije — Sun, 14 Sep 2014 15:49:54 GMT

Thanks

How can I store this in a variable. Pls explain

Re: Date gap

upaliwije — Sun, 14 Sep 2014 15:51:13 GMT

From Date is recorded in the table against each record

Re: Date gap

anbu1984 — Sun, 14 Sep 2014 16:08:54 GMT

So do you want calculate difference for all rows or one row?

Re: Date gap

upaliwije — Sun, 14 Sep 2014 16:12:31 GMT

FOR ALL ROWS

Re: Re: Date gap

maxgro — Sun, 14 Sep 2014 16:23:09 GMT

see attachment

Re: Date gap

upaliwije — Sun, 14 Sep 2014 16:50:35 GMT

THAKS FOR YOUR REPLY

BUT IT IS STILL NOT WORKING IN MY MODULE. I HAVE RECORDED FROM_DATE IN THE FOLLOWING FORMAT. IS THAT THE REASON ?

date(floor(FROM_DATE),'DD-MMM-YYYY')AS FROM_DATE,

Re: Date gap

sundarakumar — Sun, 14 Sep 2014 17:06:01 GMT

in ur load script try this,

load ....,

num(makedate(2014,8,31))-num(from_date) as days_past,

.....

from xyz;

This will giv u a field called days_past which has the required number for each and every row. Since there are more than one row u cannot straight away save it to a variable.

Please explain ur requirement, may be a field will help in ur case. since we go for variable only in case of max date, min date, etc.

-Sundar

Re: Date gap

MarcoWedel — Sun, 14 Sep 2014 19:23:16 GMT

please post sample data and script

Re: Date gap

MarcoWedel — Sun, 14 Sep 2014 19:44:28 GMT

Hi,

maybe you are looking for this?:

LET vDateToCompare =  Num(Date#('31-aug-2014', 'DD-MMM-YYYY'));

// create some random From_Dates
myTable:
LOAD date(floor(FROM_DATE),'DD-MMM-YYYY')AS  FROM_DATE;
LOAD YearStart(Today())+Rand()*($(vDateToCompare)-YearStart(Today())) as FROM_DATE
AutoGenerate 100;

//add interval between vDateToCompare and FROM_DATE
Left Join (myTable)
LOAD Distinct
  FROM_DATE,
  Interval($(vDateToCompare)-FROM_DATE) as Interval,
  DayStart($(vDateToCompare))-DayStart(FROM_DATE) as DaysBetween
Resident myTable;

hope this helps

regards

Marco

Re: Date gap

upaliwije — Mon, 15 Sep 2014 05:18:19 GMT

Thanks all