Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a date format like MM/DD/YYYY,I would like to convert it to YYYY Q format.
Thanks
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;
Hi,
Try this
year([Bookings Date (2)]) & 'Q' & CEIL(NUM(month([Bookings Date (2)]))/3)
-Sathish
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
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
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