
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Convert MMM-YY for autocalendar use
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.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try using:
Date(Date#(YourField,'MMM-YY'))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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] ;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]:
.....


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes this is working perfect for me.
Thanks for all you help.
