urgent help needed

[#1]

Hi All,

I'm having a csv file which I have used in load script. It is having 2 date fields named contract_start_date and contract_end_date.

I'm having a chart where I want to do the following 2 things

1) add one month to all the values of contract_start_date field (tried using  AddMonths( CONTRACT_START_DATE,1)) and

2) find the difference between contract_end_date and today() in months (tried using CONTRACT_END_DATE-Today())

but both of them are not working. Can anyone please help me to solve this.

Attached is my qlikview file.

Thanks in advance!!!

[Marcio_Campestrini]

Hi.

Try to format dates during the load using date function. If you need the hour, split in two fields.

[#3]

try ....

date(field_name) as date_field_name

day(field_name) as day_field_name

week(field_name) as week_field_name

month(field_name) as month_field_name

year(field_name) as year_field_name

....

[#4]

Hi

are you sure your dates are under date format and not looking as dates

chris

[#5]

Hi Kavita,

AddMonths will do the trick. I think the problem is when you pull the data in the data isn't in a date format.

As mentioned by Marcio try changing the field to a date format by doing a date in the script. One this has been resolved I'm sure you will then be able to do the calculations you need to on the date fields.

Regards,
Marius

[#6]

Hi Marcio,

I tried formatting the date using date functions but its not working.

[tresB · Accepted solution]

Date format is the issue. The date fields are not really dates, rather strings. You have to get the proper timestamp using date parsing function - Date#(). I have done for the last expression in the attached qvw. It would be better if you load the dates using Date#(), like:

LOAD BILL_PROFILE_ID,

     Date#(CONTRACT_START_DATE, 'DD-MMM-YY hh.mm.ss.ffffff tt') as CONTRACT_START_DATE

     CONTRACT_TERMS,

     Date#(CONTRACT_END_DATE, 'DD-MM-YYYY') as CONTRACT_END_DATE

From <>;

[#8]

TRY THIS

For  1st Query :-

this will give the result in numeric format

=(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())     

OR

this will give the result in date format

=DATE(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())

For 2nd Query :-

=DATE(DATE#(SubField(CONTRACT_START_DATE,' ',1),'DD-MMM-YY'),'M/DD/YYYY')

[#9]

Hi.

TRY THIS:

For 1st query:_

= AddMonths( Date#(CONTRACT_START_DATE,'DD-MMM-YY hh.mm.ssssss tt'),1)

For 2nd Query :-

this will give the result in numeric format

=(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())     

OR

this will give the result in date format

=DATE(DATE(date#(CONTRACT_END_DATE,'DD-MM-YYYY'),'M/DD/YYYY')-Today())

[#10]

Thanks a lot tresesco, Its working now