topic Re: Date convertion in QlikView

Date convertion

Tue, 23 Sep 2014 11:23:06 GMT

Hi,

I have a date format like MM/DD/YYYY,I would like to convert it to YYYY Q format.

Thanks

Re: Date convertion

senpradip007 — Tue, 23 Sep 2014 11:31:21 GMT

Try like:

=Year([Your Field]) & ' Q' & ceil(Month([Your Field])/3)

Re: Date convertion

avinashelite — Tue, 23 Sep 2014 11:33:44 GMT

Hi Rajesh,

Try like this:

Date(Ceil(datefield),'YYYY') &'Q' as newfiled

Re: Date convertion

Tue, 23 Sep 2014 12:25:11 GMT

I am not successed with above script.

ex:

Date:

8/8/2014(MM/DD/YYYY format)---i would like to convert it 2014 Q3??

any sugestions.

thanks

Re: Date convertion

anbu1984 — Tue, 23 Sep 2014 12:28:56 GMT

=SubField('8/8/2014','/',3) & ' Q' & Ceil(SubField('8/8/2014','/',1)/3)

Re: Date convertion

tresB — Tue, 23 Sep 2014 12:31:36 GMT

=Year(Date#('8/8/2014', 'MM/DD/YYYY'))&' Q'&ceil(Month(Date#('8/8/2014', 'MM/DD/YYYY'))/3)

You can replace the date here with your datefield.

Re: Date convertion

avinashelite — Tue, 23 Sep 2014 12:33:27 GMT

Hi Rajesh,

Try like this:

Date(Ceil(datefield),'YYYY') &'Q' &Ceil(SubField(datefield),'/',1)/3) as newfiled

this should work

Re: Date convertion

Tue, 23 Sep 2014 12:53:45 GMT

i didi not get expected output ,i got some thing like Q13953

Date(Ceil([Bookings Date (2)],'YYYY')) &'Q' &Ceil(SubField([Bookings Date (2)],'/',1)/3) as [Booking Quarter],

Thanks

Re: Date convertion

PrashantSangle — Tue, 23 Sep 2014 12:56:59 GMT

Hi,

Try this,

=Year(Date(DateField, 'MM/DD/YYYY'))&' Q'&ceil(Month(Date(DateField, 'MM/DD/YYYY'))/3)

Regards

Re: Date convertion

avinashelite — Tue, 23 Sep 2014 12:58:38 GMT

Hi,

try like this:

Date(Ceil([Bookings Date (2)],'YYYY')) &'Q' & Date(Ceil([Bookings Date (2)]),'MM')/3 as [Booking Quarter]

Re: Date convertion

Tue, 23 Sep 2014 13:00:46 GMT

Base:
LOAD EMPNO,
    ENAME,
    JOB,
    MGR,
    date(HIREDATE,'DD/MM/YYYY') as HIREDATE,
    SAL,
    COMM,
    DEPTNO;
SQL SELECT *
FROM SCOTT.EMP;

minMaxdate:
Load min(HIREDATE,'DD/MM/YYYY') as minDate, max(HIREDATE,'DD/MM/YYYY') as maxDate Resident Base;

Let vminDate = num(peek('minDate',0,'minDate'));
Let vmaxDate = num(peek('maxDate',0,'maxDate'));

cal1:
load
    IterNo() as num1,
    $(vminDate) + IterNo() - 1 as Num,
    date($(vminDate) + IterNo()-1) as TempDate
AutoGenerate 1 While
$(vminDate)+IterNo()-1 <= $(vmaxDate);

cal2:
load
    Num as DateSeq,
    TempDate as TheDate,
    Month(TempDate) as month,
    num(Month(TempDate)) as MonthSeq,
    Year(TempDate) as yearSeq,
    day(TempDate) as DaySeq
Resident cal1
order by TempDate ASC;

//drop table cal1;

cal3:
LOAD
    DateSeq,
    TheDate as HIREDATE,
    yearSeq,
    month,
    MonthSeq,
    DaySeq,
    MonthSeq + (yearSeq - 1) *12 as MonthSeq1,
    Ceil(MonthSeq/3) as quarter,
    'Q'&     Ceil(MonthSeq/3) as quarter1,
    WeekDay(TheDate) as DayName
Resident cal2
order by TheDate ASC;

Re: Date convertion

sathishkumar_go — Tue, 23 Sep 2014 13:11:48 GMT

Hi,

Try this

year([Bookings Date (2)]) & 'Q' & CEIL(NUM(month([Bookings Date (2)]))/3)

-Sathish

Re: Date convertion

Tue, 23 Sep 2014 13:17:33 GMT

Hi Max,

Look your script is fine but one thing is one extra 'Q' is placed.

I dont want this "Q". There is no record also associated with "Q" when I click it. I need Q1, Q2, Q3 and Q4.

list box

---------

2014 Q3

2014 Q2

Re: Date convertion

PrashantSangle — Tue, 23 Sep 2014 14:48:26 GMT

Hi,

Try this,

If(not isNull(Floor(DateField)),

Year(Date(DateField, 'MM/DD/YYYY'))&' Q'&ceil(Month(Date(DateField, 'MM/DD/YYYY'))/3) )

if(len(trim(DateField))>0,

Year(Date(DateField, 'MM/DD/YYYY'))&' Q'&ceil(Month(Date(DateField, 'MM/DD/YYYY'))/3)

)

Regards

Re: Date convertion

MarcoWedel — Tue, 23 Sep 2014 15:57:41 GMT

Dual(Year([Your Field]) & ' Q' & ceil(Month([Your Field])/3), QuarterStart([Your Field]))

To get an underlying numerical value that enables you to perform date calculations with the newly created field.

Regards

Marco