topic Re: Difference between two days in QlikView

Difference between two days

Thu, 22 Sep 2011 09:00:00 GMT

I have a table which contain two coloumn.

Let the first Coloumn is Date and second Column is Value.

where, value = (Today() - Date)

It shown like:----

Date Value

18/04/11 99

18/04/11 100

10/09/11 155

10/09/11 139

10/09/11 136

I use the date format as DD/MM/YYYY

It's not shown the correct value. Please help me!!!!!!!

Difference between two days

Thu, 22 Sep 2011 09:10:02 GMT

Hi Anup,

The format must be same for both the Date and Today()

value = (Date(Today(),'DD.MM.YYY') - Date(Date,'DD.MM.YYYY')

Hope that helps

Re: Difference between two days

its_anandrjs — Thu, 22 Sep 2011 09:10:59 GMT

Hi,

Make proper date format for this like

Date( DateField, ' DD/MM/YYYY ' ) as Date

HTH

Rgds

Anand

Re: Difference between two days

its_anandrjs — Thu, 22 Sep 2011 09:15:16 GMT

Hi,

For

Date use

Date = Date( DateField, ' DD/MM/YYYY ' )

Value use

value = ( Date( Today(),'DD/MM/YYYY' ) - Date( Date,'DD/MM/YYYY' )

Let me know about this.

Rgds

Anand

Re: Difference between two days

its_anandrjs — Thu, 22 Sep 2011 09:48:55 GMT

Hi,

Hope you got correct answer, if so mark it correct answer onmy post.

Let me know abt this.

Rgds

Anand

Difference between two days

Thu, 22 Sep 2011 09:49:49 GMT

No anand it's not working

It's till shown---

date value

02/04/11 26

02/04/11 17

02/04/11 13

02/04/11 14

02/04/11 23

I use pivot table.

Difference between two days

SunilChauhan — Thu, 22 Sep 2011 09:56:39 GMT

it might be timestamp issue

please subtract two timestamp values and then see the result.

Difference between two days

its_anandrjs — Thu, 22 Sep 2011 10:00:33 GMT

Hi,

Check your date format in SET command

SET DateFormat='M/D/YYYY';

Check this in your load script what is written there or other wise

use Date#( ) command to make dates

Rgds

Anand

Difference between two days

Thu, 22 Sep 2011 10:09:53 GMT

Sunil,

What is timestamp issue. I don't know actually. Please tell me the detail about that.

Difference between two days

SunilChauhan — Thu, 22 Sep 2011 10:13:03 GMT

actually timestamp is a format where date contain time also with date

12/01/2011 23:45

may be your two difference below is due to time stamp

18/04/11 99

18/04/11 100

Difference between two days

Thu, 22 Sep 2011 10:20:49 GMT

Then why I get this kind of result??

date value

02/04/11 26

02/04/11 17

02/04/11 13

02/04/11 14

02/04/11 23

Difference between two days

its_anandrjs — Thu, 22 Sep 2011 10:24:25 GMT

Hi,

I think it is not issue of timestamp, can you upload small application with few data so i can check that or i want to ask from where you load this data it is excel file if so please once check the format.

Rgds

Anand

Difference between two days

SunilChauhan — Thu, 22 Sep 2011 10:28:25 GMT

anaand

i agree with you anand but can plese tell me what are different part which make a timestamp.

please describe.

anup

can please upload a sample and mention what is your actual output u want.

Re: Difference between two days

Thu, 22 Sep 2011 11:03:08 GMT

Anand I load this data from SQL Server. Not from Excel. Please check the attachment, I send you the file as Excel format...

Re: Difference between two days

Thu, 22 Sep 2011 11:06:15 GMT

Please check the attachment, I send you the file as Excel format...

Difference between two days

SunilChauhan — Thu, 22 Sep 2011 11:12:42 GMT

if (Billdate=today(),Amount) -Amount

hope this helps

Re: Difference between two days

its_anandrjs — Thu, 22 Sep 2011 11:16:14 GMT

Hi,

See the updated file i load your file and make new date as

Date(Date#(BillingDate,'DD/MM/YYYY')) AS New_Date

See the attached sample file.

Let me know abot this.

Rgds

Anand

Difference between two days

Jason_Michaelides — Thu, 22 Sep 2011 11:18:25 GMT

Based on your supplied data:

LOAD

RangeName,

BillingDate,

Today()-BillingDate AS [Value(Difference)],

Amount

FROM

[CH01_20110922_161855.xls]

(biff, embedded labels, table is Sheet1$);

Seems to work just fine, so as Sunil suggested there may be an issue with your raw data. Can you post some data straight from SQL for us to look at, rather than exporting the (probably incorrect) data from a QlikView output?

Thanks,

Jason