topic Help with a If calculation in QlikView

Help with a If calculation

Fri, 02 Mar 2012 12:05:11 GMT

I have two datefields and want the diffrens in between them. I do this by Stopdate - StartDate.

But in some fields the StopDate are wrong. So I only want the calculation for the fields were Stopdate are larger then Startdate. I do the caluclation in the script as Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD') - Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD') as DayDiff

I guess I should use a IF statemeant, can anyone help me?

Thanks!

Re: Help with a If calculation

Miguel_Angel_Baeyens — Fri, 02 Mar 2012 12:09:01 GMT

Hi,

If I understand correctly, you only need to add a conditional so the expression is only calculated when StopDate is greater than StartDate, so the following should do it:

If(StopDate > StartDate,
     Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD') - Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD') 
     , 0) as DayDiff // 0 is the default value when StopDate is not greater than StartDate: change it according to your needs

Hope that helps.

Miguel

Help with a If calculation

CELAMBARASAN — Fri, 02 Mar 2012 12:09:15 GMT

Hi,

Try with this.

If(Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD')>Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD'),Interval(Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD')-Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD'),'D')) as DayDiff

Hope it helps

Celambarasan

Help with a If calculation

Fri, 02 Mar 2012 12:26:55 GMT

Thanks to both of you!