topic Writing a Expression to subract dates, in QlikView

Writing a Expression to subract dates,

Tue, 06 Mar 2012 11:42:04 GMT

Hi,

I want to write a expression in which i need to subtract the two dates field and i need to convert the result field in to hours, days and minutes. I am looking for the syntax to follow and little confused with the functions to use,

Any help will be highly appreciated,

Thanks,

Praveen.

Writing a Expression to subract dates,

CELAMBARASAN — Tue, 06 Mar 2012 11:51:17 GMT

Hi,

Use interval function

interval(MaxDate-MinDate,'D hh:mm')

Hope it helps

Celambarasan

Writing a Expression to subract dates,

Tue, 06 Mar 2012 12:05:21 GMT

Celambarasan is already answered it...

FYI for getting the data in hours... change the format to interval(EndDate-StartDate,'hh:mm:ss')

Writing a Expression to subract dates,

jagan — Tue, 06 Mar 2012 12:05:40 GMT

Hi,

Use Interval(), for getting the difference

Ex:

Interval(StartDate - EndDate, 'D hh:mm')

Regards,

Jagan.

Writing a Expression to subract dates,

Tue, 06 Mar 2012 12:15:51 GMT

Hi,

I am not getting correct results, by default in the output it throws the date columns and it shows hours and seconds. I want to display only hours and seconds and most importantly i need to sum it.

For eg i need to sum(hours) and sum(minutes) and display it one row.

Any help towards this,

Writing a Expression to subract dates,

kaushiknsolanki — Tue, 06 Mar 2012 12:24:01 GMT

Try this

num#(intervalmatch(Date1 - Date2,'d'),'##') as Day

num#(intervalmatch(Date1 - Date2,'hh'),'##') as Hour

Now you can sum them

Regards,

Kaushik Solanki

Writing a Expression to subract dates,

Tue, 06 Mar 2012 12:43:27 GMT

still am not getting the correct answer, the above syntax doesnot works. I need to remove match in that, only then it works.

Writing a Expression to subract dates,

kaushiknsolanki — Tue, 06 Mar 2012 12:45:44 GMT

Hi,

Oh yes its my typing mistake, apologies for that.

Are you getting correct answer when you use Interval in above expression?

Regards,

Kaushik Solanki

Writing a Expression to subract dates,

Tue, 06 Mar 2012 12:47:31 GMT

still i am not getting, one of the columns used in interval function also throws in output, i need only the hrs and minutes,

Any help??

Writing a Expression to subract dates,

CELAMBARASAN — Tue, 06 Mar 2012 12:48:21 GMT

Hi,

Can you please tel me about your problem in brief ?

Celambarasan

Writing a Expression to subract dates,

kaushiknsolanki — Tue, 06 Mar 2012 12:49:05 GMT

Hi,

Can you please share what you tried and what u expect.

If possible pls post the qvw file.

Regards,

Kaushik Solanki

Writing a Expression to subract dates,

Tue, 06 Mar 2012 12:54:39 GMT

interval(DISCHARGE_TIME - ADMISSIONTIME,'hh:mm:ss') am using this expression in the output it shows like

('2010-11-12' 55:11:22)

('2011-15-16' 42:10:01)

I need only hrs:mm:ss in the output and i dont need date in the output. Also i need to sum all the hrs, minutes and seconds and display it in one row,

For above eg,

the output i like to see is 97:21:23..

Any suggestions to write expressions?

Writing a Expression to subract dates,

kaushiknsolanki — Tue, 06 Mar 2012 12:59:37 GMT

HI,

It seems that the data formate for DATE field is not proper.

If possible please sens us the sample file.

Regards,

Kaushik Solanki

Writing a Expression to subract dates,

Tue, 06 Mar 2012 13:09:58 GMT

Hi,

How to upload the sample file i find no ways to attach the file.

Kaushik Solanki wrote:
HI,
It seems that the data formate for DATE field is not proper.
If possible please sens us the sample file.
Regards,
Kaushik Solanki

Writing a Expression to subract dates,

CELAMBARASAN — Tue, 06 Mar 2012 13:11:44 GMT

Hi,

Use Advanced editor which is right of the reply editor.

Celambarasan

Re: Writing a Expression to subract dates,

Wed, 07 Mar 2012 04:14:26 GMT

Hi,

Pls find the qvw file. Please find the last tab of the report.

Thanks,

Praveen.

Writing a Expression to subract dates,

Wed, 07 Mar 2012 04:55:08 GMT

i have attached the qvw file. check to it.

Re: Writing a Expression to subract dates,

jagan — Wed, 07 Mar 2012 05:35:47 GMT

Hi,

Check the attached file, I added the dimensions Discharge and Admission Time and removed D in the Time format. It displays the difference between the two in hours: seconds format.

Regards,

Jagan.

Re: Writing a Expression to subract dates,

Wed, 07 Mar 2012 05:42:08 GMT

Hi,

can you send me the expression alone here, i cant open the qvw u posted , since i am using personal edition.

Thanks,

Praveen.

Writing a Expression to subract dates,

jagan — Wed, 07 Mar 2012 05:45:10 GMT

Hi,

I used this expression

=interval( ADMISSIONTIME - DISCHARGE_TIME,'hh:mm')

Regards,

Jagan.