Solved: How do I create a date from JAN2013 - Qlik Community

Report Inappropriate Content · ‎2016-09-09

My date file that am importing has the Date as JAN2013 how do I make a date from that?

miguelbraga · ‎2016-09-15

Even try this as multiple date formats because you refered first as JAN2013, then Jan12 or Jan-12:

Alt(

Date((Date#(Date, 'MMM-YY')), 'DD/MM/YYYY'), // for case Date comes with Jan-12

Date((Date#(Date, 'MMMYY')), 'DD/MM/YYYY'), // for case Date comes with Jan12

Date((Date#(Date, 'MMMYYYY')), 'DD/MM/YYYY'), // for case Date comes with JAN2013

'Invalid Format' // If date comes with an invalid format

) as Date

Regards,

MB

View solution in original post

MindaugasBacius · ‎2016-09-09

Like this:

=Date((Date#('JAN2013', 'MMMYYYY'), 'YYYY-MM-DD')

The result looks like that:

sunny_talwar · ‎2016-09-09

Or may be this:

Date(MakeDate(Right(Date,4), Left(Date, 3), 1)) as Date

Replace 1 with whatever date you want to create.

Anil_Babu_Samineni · ‎2016-09-09

What are you expecting

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Report Inappropriate Content · ‎2016-09-13

I am attempting to be able to use a Max Date variable to use in set analysis, and be able to run things like year to date nut just can't get it to work. My Qv skills are a bit rusty

sunny_talwar · ‎2016-09-13

Did you get it to work, or are you still trying to figure this out?

MarcoWedel · ‎2016-09-13

LOAD *,
          Month(MonthYear) as Month,
          Year(MonthYear) as Year;
LOAD Date#(YourDate,'MMMYYYY') as MonthYear
From YourSource;

hope this helps

regards

Marco

Report Inappropriate Content · ‎2016-09-14

Still trying to get it to work ......

sasikanth · ‎2016-09-14

HI,

It depends on the granularity level of your data ,check weather it is month level or day level.

please do share a sample of your data.

Report Inappropriate Content · ‎2016-09-14

Data is at Month level, I have attached a small sample set below

			Value
Store A	Product Cat	Jan12	60859
		Feb12	52132
		Mar12	56551
		Apr12	59750
		May12	58010
		Jun12	55000
		Jul12	57081
		Aug12	56238
		Sep12	56196
		Oct12	58242
		Nov12	57266
		Dec12	73807
		Jan13	67466
		Feb13	59402
		Mar13	75607
		Apr13	64579
		May13	62954
		Jun13	68829
		Jul13	58097
		Aug13	58301
		Sep13	59175
		Oct13	63455
		Nov13	64180
		Dec13	105383
		Jan14	88205
		Feb14	78795
		Mar14	96060
		Apr14	108033
		May14	148180
		Jun14	103311
		Jul14	116351
		Aug14	106459
		Sep14	100022
		Oct14	94334
		Nov14	93256
		Dec14	158555
		Jan15	85170
		Feb15	74543
		Mar15	84702
		Apr15	89426
		May15	87764
		Jun15	83988
		Jul15	73914
		Aug15	66165
		Sep15	65115
		Oct15	68060
		Nov15	69751
		Dec15	85937
		Jan16	64784
		Feb16	57578
		Mar16	67451
		Apr16	78640