Wrong dates

[nareshthavidishetty]

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..

[pra_kale]

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

[maniram23]

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 .

[maxgro]

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....

an example in the image

[HirisH_V7]

Hi,

Can you share sample of your original Data without transformations.

that will be helpful to look into the Format.

-Hirish

[Peter_Cammaert]

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

[maniram23]

Hi,

I Think this is useful for you..

Check it.

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