topic Re: Special Characters in Formatting in QlikView

Special Characters in Formatting

Thu, 30 Apr 2015 08:33:49 GMT

Hi,

I need to add a "/" within my date format (script). Date('01.01.2015', 'MM/YYYY'), but I don't get the expected result. Instead the month is divided by the year.

Can someone help?

Tanks in advance!

Re: Special Characters in Formatting

prma7799 — Thu, 30 Apr 2015 08:38:40 GMT

use this

=Date(Date#('01.01.2015', 'DD.MM.YYYY'),'DD/MM/YYYY')

Re: Special Characters in Formatting

Anonymous — Thu, 30 Apr 2015 08:39:57 GMT

Hi,

Find the following Date# Function i think it will help you.

Date#

date#(expression [ , format-code ])

The date# function evaluates the expression as a date according to the string given as format-code. If the format code is omitted, the default date format set in the operating system is used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY

Re: Special Characters in Formatting

vikramv — Thu, 30 Apr 2015 08:52:35 GMT

Apart from above you can also use

=Date(Replace('01.01.2015','.','/'),'MM/YYYY')

Thanks,

Vikram

Re: Special Characters in Formatting

Thu, 30 Apr 2015 09:08:03 GMT

thank you, but it still doesn't work. Maybe the context helps:

We have it as a variable:

LET ReportingMonth = date(date#(MonthStart(Today(),-1),'YYYY-MM-DD'),'MM/YYYY');

Re: Special Characters in Formatting

prma7799 — Thu, 30 Apr 2015 09:11:02 GMT

have you check your date format like

SET DateFormat='M/D/YYYY';

In the edit script some few set statements are there.

Re: Special Characters in Formatting

sujeetsingh — Thu, 30 Apr 2015 09:11:50 GMT

I think you can achieve it with many ways. Mehl has provided you a way

Date(replace(DateField,'.','/'),'MM/DD/YYYY')

Re: Special Characters in Formatting

vikramv — Thu, 30 Apr 2015 09:12:40 GMT

Hi Mehl, Your Expression is Wrong use this.

LET ReportingMonth = date(date#(MonthStart(Today(),-1)),'MM/YYYY')

Re: Special Characters in Formatting

Thu, 30 Apr 2015 09:22:27 GMT

We have the following DateFormat:

SET DateFormat='YYYY-MM-DD';

Therefore we used:

LET ReportingMonth = date(date#(MonthStart(Today(),-1),'YYYY-MM-DD'),'MM/YYYY');

Re: Special Characters in Formatting

Thu, 30 Apr 2015 09:24:25 GMT

Unfortuneately the expressions doesn't work. It has the same result as mine: 0.001488....

Re: Special Characters in Formatting

Thu, 30 Apr 2015 09:28:05 GMT

Doesn't work for me. When using the variable the value is null

Re: Special Characters in Formatting

Thu, 30 Apr 2015 09:29:38 GMT

When using LET ReportingMonth = Date(replace(today(),'.','/'),'MM/DD/YYYY'); the result is "6,1670..."

Re: Special Characters in Formatting

Thu, 30 Apr 2015 09:30:44 GMT

it does work in an expression but not in script

Re: Special Characters in Formatting

vikramv — Thu, 30 Apr 2015 09:41:50 GMT

Hi,

U should use set.

Set ReportingMonth = date(date#(MonthStart(Today(),-1)),'MM/YYYY')

And then use as $(ReportingMonth)

Thanks,

Vikram.

Re: Special Characters in Formatting

Thu, 30 Apr 2015 10:10:08 GMT

Thanks! I used "LET", but it works fine with "SET"

Re: Special Characters in Formatting

Anonymous — Thu, 30 Apr 2015 10:11:31 GMT

Hi,

if you use "LET" you don't have to use "$" when you want to display the value of the variable.

If you use "SET" yuo must use "$" when you use your variable.

Elena