topic Convert date from CYYMMDD and get diff from today in QlikView

Convert date from CYYMMDD and get diff from today

Wed, 07 Mar 2012 10:57:37 GMT

Hi Everyone , I thought this should be a simple task - even for a newbie , but I'm going round in circles ! I simply need to convert a date from CYYMMDD and get the number of days between now and a due date. I can reformat the date , but then I can't use the new date to get the number of days difference. Date format is currently set as DD/MM/YYYY. Can someone give me a clue please ! Thanks , Simon

Convert date from CYYMMDD and get diff from today

CELAMBARASAN — Wed, 07 Mar 2012 11:00:08 GMT

Hi,

Try with this using interval function.

Interval(Today()-Date#(Right(DateField,6),'YYMMDD'),'D')

Hope it helps

Celambarasan

Convert date from CYYMMDD and get diff from today

Wed, 07 Mar 2012 11:13:44 GMT

Hi ,

Excellent - however , a couple of questions. Can this be used in a similar way to networkdays to omit weekends ? This gives negative number of days - can the fields be reversed ?

Many thanks, Simon

Convert date from CYYMMDD and get diff from today

CELAMBARASAN — Wed, 07 Mar 2012 11:30:52 GMT

Hi,

Ya you can use in it

NetworkDays(Date#(Right(DateField,6),'YYMMDD'),Today())

Celambarasan

Convert date from CYYMMDD and get diff from today

Wed, 07 Mar 2012 11:48:57 GMT

Hi , have applied the following

NetworkDays(Date#(Right(DUEDT,6),'YYMMDD'),Today()) as Diff,

but it only gives a value for past due dates - not future ones

Regards,

Simon

Convert date from CYYMMDD and get diff from today

CELAMBARASAN — Wed, 07 Mar 2012 11:54:16 GMT

Hi,

For this case use condition

If(Date#(Right(DUEDT,6)<=Today(),NetworkDays(Date#(Right(DUEDT,6),'YYMMDD'),Today()),

-NetworkDays(Today(),Date#(Right(DUEDT,6),'YYMMDD'))) as Diff

Hope it helps

Celambarasan

Convert date from CYYMMDD and get diff from today

Wed, 07 Mar 2012 13:05:54 GMT

Hi , sorry - can't get that one to work , syntax error with missing closing bracket , can't get one added to work. Don't waste too much time on it - can probably work with the negative values.

Regards,

Simon

Convert date from CYYMMDD and get diff from today

CELAMBARASAN — Wed, 07 Mar 2012 13:12:30 GMT

Hi,

Check now

=If(Date#(Right(DUEDT,6)<=Today()),NetworkDays(Date#(Right(DUEDT,6),'YYMMDD'),Today()),

-NetworkDays(Today(),Date#(Right(DUEDT,6),'YYMMDD')))

Celambarasan

Convert date from CYYMMDD and get diff from today

Wed, 07 Mar 2012 13:38:26 GMT

Hi , this one just gives zero's for Diff - have now cheated and multiplied the Interval version by -1 to change the sign.

Thanks so much for you help - really appreciate it.

Regards,

Simon