topic Re: how do i generate year, Half yr, quarter, weekly & day from this date format in QlikView

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

Anonymous — Wed, 20 Aug 2014 20:11:13 GMT

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

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

tobias_klett — Wed, 20 Aug 2014 20:24:56 GMT

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

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

ecolomer — Wed, 20 Aug 2014 20:29:15 GMT

See the file

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

boraste-sagar — Sat, 29 Sep 2018 15:19:09 GMT

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;