topic Re: Wrong dates in QlikView

Wrong dates

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

The below logic is used to derive dates in qlikview,

Date#(Num(Month([REPORTDT]))&'/01/'&(Year([REPORTDT])-1), 'MM/DD/YYYY') as ReportingDate

But it gives dates as

1/01/4994

3/01/4993......

Which is not an correct dates.

Thanks..

Re: Wrong dates

pra_kale — Sun, 10 Apr 2016 05:45:58 GMT

Hi,

Pl check below given blog on formats of the dates.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

Re: Wrong dates

maniram23 — Sun, 10 Apr 2016 05:46:39 GMT

Hi,

In the REPORTDT what type of data you having.

Can you please share the data.

Other wise replace date# in the place of date .

Re: Wrong dates

maxgro — Sun, 10 Apr 2016 06:21:58 GMT

if your field REPORTDT is a text, you need the date# function to interpret the date

date#(REPORTDT, 'format of reportdt')

and then, to format the date

date(date#(REPORTDT, 'format of reportdt'), 'format of reporting date')

OR (this use the date format of the SET DateFormat you can find at the beginning of your script)

date(date#(REPORTDT, 'format of reportdt'))

your script should be

.....

date(date#(REPORTDT, 'format of reportdt'), 'format of reporting date') as ReportingDate

....

format code are here

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/conventions-number-time-formats.htm?q=format%20code

an example in the image

Re: Wrong dates

HirisH_V7 — Sun, 10 Apr 2016 06:36:35 GMT

Hi,

Can you share sample of your original Data without transformations.

that will be helpful to look into the Format.

-Hirish

Re: Wrong dates

Peter_Cammaert — Sun, 10 Apr 2016 08:21:58 GMT

Using Massimo's explanation as a starting point, there is an easier way to get the date of the first of the month. In the next example, replace format of reportdt with the actual format of the input dates in field REPORTDT. Because that's probably where the problems with dates 2900 years in the future start:

MonthStart(Date#(REPORTDT, 'format of reportdt')) AS ReportingDate,

This expression will use the default Date Format String (see the SET statements at the top of your script) to store reporting dates. If you want to change the format of values in ReportingDate, add a call to the Date() function to modify the result to your taste.

Peter

Re: Wrong dates

maniram23 — Mon, 11 Apr 2016 13:12:19 GMT

Hi,

I Think this is useful for you..

Check it.

Date(Num(Month([REPORTDT]))&'/01/'&(Year([REPORTDT])-1))