topic Re: Date Format in QlikView

Date Format

Mon, 15 Dec 2014 07:41:01 GMT

I have a list box which calculates Week days in a month for every week.

It is currently showing single digits for days 1 - 9.

I would like these to show with double digits 02 - 09. How can I do this?

This is the script I use to generate the dates

Dual(if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))
&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) )),if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))) AS Weeks

Re: Date Format

ali_hijazi — Mon, 15 Dec 2014 07:44:18 GMT

num(day(WeekStart( RE_DAY) )),'00')

Re: Date Format

Mon, 15 Dec 2014 07:50:59 GMT

Put in another condition: if(len(num(day(WeekStart(RE_DAY))))=1, '0'&num(day(WeekStart( RE_DAY) ))) or

if(num(day(WeekStart(RE_DAY)<10,'0'&num(day(WeekStart(RE_DAY))))

I hope it helped!

Re: Date Format

saumyashah90 — Mon, 15 Dec 2014 08:14:36 GMT

num(day(WeekStart(Data) ),'00')

Response to Ali is good with one correction of bracket.

num(day(WeekStart(RE_DAY)),'00')

Re: Date Format

Mon, 15 Dec 2014 08:25:30 GMT

The above solutions do not work as desired. Would you mind maybe re-writing the full expression and adding your parts in it?

Re: Date Format

jagan — Mon, 15 Dec 2014 08:27:42 GMT

Hi,

Use

'01' instead of 1

and Num(day(WeekStart( RE_DAY) ), '00') instead of day(WeekStart( RE_DAY) )

=Dual(if( month(WeekStart( RE_DAY) )<> month( RE_DAY), '01' , Num(day(WeekStart( RE_DAY) ), '00'))

&' - '&

if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), num(day(RE_DAY), '00') , Num(day(WeekEnd( RE_DAY) ), '00')),if( month(WeekStart( RE_DAY) )<>month( RE_DAY), '01' , Num(day(WeekStart( RE_DAY) ), '00')))

Hope this helps you.

Regards,

Jagan.

Re: Date Format

saumyashah90 — Mon, 15 Dec 2014 08:29:38 GMT

Dual(if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , num(day(WeekStart( RE_DAY) ),'00'))
&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , num(day(WeekEnd( RE_DAY) ),'00')),if( month(WeekStart( RE_DAY) )<>

month( RE_DAY), 1 , num(day(WeekStart( RE_DAY) ),'00'))) AS Weeks

Please check on the brackets properly

Re: Date Format

Mon, 15 Dec 2014 08:57:29 GMT

Thanks! I have applied the above solution ansd it works, except it does not cater for te 1st of any month.

Why is that and how can I fix this?

Re: Date Format

jagan — Mon, 15 Dec 2014 09:14:29 GMT

Hi,

Check this

Dual(if( month(WeekStart( RE_DAY) )<> month( RE_DAY), '01' , num(day(WeekStart( RE_DAY) ),'00'))
&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), Num(day(RE_DAY), '00') , num(day(WeekEnd( RE_DAY) ),'00')),if( month(WeekStart(RE_DAY) )<>

month( RE_DAY), '01' , num(day(WeekStart( RE_DAY) ),'00'))) AS Weeks

Regards,

Jagan.

Re: Date Format

saumyashah90 — Mon, 15 Dec 2014 09:17:21 GMT

As mohan said

Use this month( RE_DAY), '01' insted of month(RE_DAY),'1'

Re: Date Format

MarcoWedel — Mon, 15 Dec 2014 09:39:43 GMT

Dual(num(if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) )),'00')
&' - '&
Num(if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) )),'00'),if(month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))) AS Weeks

Re: Date Format

Mon, 15 Dec 2014 11:11:01 GMT

Thanks to all of you guys! You make my learning so much fun and easy