Convert MMM-YY for autocalendar use

[tomf122]

I am trying to use the auto calendar Qlicksense logic in the data loader but having trouble as the field (Month) I am using is currently in the format MMM-YY (eg May-22). Is there a way to adjust this field that I can then use the auto calendar. The field which holds the data is created by using the cross table function so unsure if this would complicate it further. Any guidance would be appreciated.

[Or]

Try using:

Date(Date#(YourField,'MMM-YY'))

[tomf122]

I have tried the below but still having issue's with it. I am doing it in the correct location or does it need to be done before hand. Logic in red is what I have added. A sample value would be 'Apr-18'

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Date(Date#(Month,'MMM-YY'))] USING [autoCalendar] ;

[Or]

I believe you would need to create an actual field using that syntax, and then derive from that field. I don't think you can derive based on a formula rather than a field (but I'm not sure, since I rarely if ever use the Derive option).

Note that you could also convert what appears to be a text field into a date formatted with the same text format, and then use that field for both derive and display:

Load Date(Date#(Month,'MMM-YY'),'MMM-YY') as Month

From YourTable;

[tomf122]

I seem to be having trouble calling the field which is created in the crosstable to change the format. As the field i need is created in the crosstable i am trying to create a table name for it so it can be used later. I am calling my table SIOP which is then followed by a long loading statement.

CrossTable (Month,Value, 9)

[SIOP]:
LOAD

.................

When i am trying to call it later i am getting an error saying the table doesnt exist. Is there a step which i am missing out on ? 

Load Date(Date#(Month,'MMM-YY'),'MMM-YY') as Month
FROM [SIOP];

[autoCalendar]:

.....

[Or · Accepted solution]

I'm not quite sure what you're doing inside the Crosstable section, so I can't really say. You could always do this the long way around:

SIOP:

Load YourStuff

From YourTable;

JOIN

Load distinct Month, Date(Date#(Month,'MMM-YY'),'MMM-YY') as Month2

Resident SIOP;

Drop Field Month;

Rename Field Month2 to Month;

// And then derive or whatnot

[tomf122]

Yes this is working perfect for me.

Thanks for all you help.