Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date convertion

Hi,


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

Thanks

14 Replies
Not applicable
Author

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;

sathishkumar_go
Partner - Specialist
Partner - Specialist

Hi,

Try this

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

-Sathish

Not applicable
Author

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

Q

PrashantSangle

Hi,

Try this,

If(not isNull(Floor(DateField)),

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

or

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

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

)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
MarcoWedel

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