Date format text box

[#1]

Hi Guys,

I have a text box formula and I wish for it to give me yesterdays dates , if yesterday was a weekday , if its not then give me fridays date.

The following is correct where it comes to the Date part of it but it is resulting in 13/00/2012

I have fluted around with it and always the Month is 000,

Can anyone advise please ?

=date(if(weekday(today()-1) * 1 <5,today()-1,today()-3),'dd/mm/yyyy')

[#2 · Accepted solution]

date(if(weekday(today()-1) * 1 <5,today()-1,today()-3),dd/mm/yyyy)

Silly me took off the quotation marks and we're hunky dory

Sorry

[hic]

No, the single quotes should be there. But the format code is (sometimes) case sensitive: 'mm' means minutes and 'MM' means month. So the date function should be date( ... ,'DD/MM/YYYY') 

/HIC

[#4]

Thanks Henric

[#5]

Wait Henric,

How come removing the quotation marks worked then , is there a detriment to just taking then off

Im not familiar with alot of the nuances of Qlikview any info would be great

Thanks again
Anne

[hic]

If you don't have the quotes, then QlikView will interpret dd/mm/yy as the field "dd" divided by the the field "mm" divided by the field "yy". But since there are no such fields, this expression will evaluate as NULL.

This means that you have a date function with NULL as second parameter - then QlikVIew will use the default date format defined in the SET statement in the beginning of the script. And then the date function works...

You could simplify everything by omitting the second parameter of the date function.

/HIC

[#7]

Thanks Henric

I understand a little better now