how do i generate year, Half yr, quarter, weekly & day from this date format

[Anonymous]

Hello,

Please find attached how do i generate from the date format Yr, Half yr quarter, weekly and day if possible, on different fields and listbox?

Rgards

[tobias_klett · Accepted solution]

Hi otanzyone,

your field does not have the same format for all entries. If it was APR 2013,... meaning MMM YYYY you could use the date interpreting function like this:

date#(Fieldname,'MMM YYYY') as Date

And also build more field with it like this:

month(date#(Fieldname,'MMM YYYY')) as Month,

'Q'&ceil(month(date#(Fieldname,'MMM YYYY'))/3) as Quarter,

'HY' & ceil(month(date#(Fieldname,'MMM YYYY'))/6) as HalfYear,

...

Hope this helps

Tobias

[ecolomer]

See the file

[boraste-sagar]

Quartersmap:

Mapping Load

RowNo() as Month,

'Q' & Ceil(RowNo()/3)as Quarter

AutoGenerate (12);

Halfyearsmap:

Mapping Load

RowNo() as Month,

'HY' & ceil(RowNo()/6) as HalfYear

AutoGenerate (12);

Temp:

LOAD

Min(Opendate) as Mindate,

Max(Opendate) as Maxdate

Resident Main;

LET vmindate= Num(Peek('Mindate',0,'Temp'));

LET vmaxdate= Num(Peek('Maxdate',0,'Temp'));

DROP Table Temp;

Calendar:

LOAD

$(vmindate) + IterNo() -1 as Num,

    Date($(vmindate) + IterNo() -1) as Date

AutoGenerate 1

While

($(vmindate) + IterNo() -1)<= $(vmaxdate);

Master_Cal:

LOAD

Floor(Date) as Opendate,

Year(Date) as Year,

       ApplyMap('Quartersmap',Month(Date),Null()) as Quarter,

       ApplyMap('Halfyearsmap',Month(Date),Null()) as HalfYear,

Month(Date) as Month,

MonthName(Date) as MonthName,

Day(Date) as Day,

YearToDate(Date) as YTD,

Week(Date) as Week

Resident Calendar;

DROP Table Calendar;