Convert date field to Q1 Q2...

[#1]

Hello,

First, thank you for your assistance in helping me with the below issue.

I used the script below in the Expression field to get the quarter name

QuarterName([Date Rqstd])

Then to get Q1, Q2, Q3, Q4. I changed the above script to the one below.

'Q' & Ceil(Month([Date Rqstd])/3)

But as you can see there is an extra value "Q".  I checked the field data and there are no nulls.  how do I get it to just show Q1, Q2, Q3, Q4 in the listbox? Again, thank you for your help!

[#2]

Hi,

try this,

'Q' & Ceil(month(Date([Date Rqstd]))/3)

[#3]

it woks without including (as Quarter)

[#4]

it works without including (as Quarter)

[pho3nix90]

I would suggest to add a where clause to your calendar.

Where Len (Trim([Date Rqstd]))>0

[sunny_talwar]

Where exactly are you using this my friend? Where clause?

[#7]

hi,

In the listbox itself - instead of using the field. I use an expression.

[#8]

here is the script:

LOAD [Assigned To],
[Bus. or Dept.],
Leaders,
Comments________________________________,
[T-Shirt Sz],
[Actual Compl. Date],
[Applic. Category],
[Request Title (Linked to SR Doc.)],
[Request Description],
[Demand Type],
Status,
[Rqstd. Compl.],
Created,
Domain,
[Est. Compl. Date],
[Finance Relevant],
ID,
[IS&T Group],
Request#,
[Date Rqstd],
MakeDate(year([Date Rqstd])) as [Year Requested],
[Primary Contact],
Priority,
[BPOC Relevant],
Requestor__,
Site,
[Yr Compl. a/o Closed],
ListID,
%Key_xml_98C49F546A18E856
FROM
[Service.qvd]
(qvd)
WHERE
MakeDate(year([Date Rqstd])) >= '1/1/2014' and IsNull([Date Rqstd])=0;

[#9]

here is the script:

LOAD [Assigned To],
[Bus. or Dept.],
Leaders,
Comments________________________________,
[T-Shirt Sz],
[Actual Compl. Date],
[Applic. Category],
[Request Title (Linked to SR Doc.)],
[Request Description],
[Demand Type],
Status,
[Rqstd. Compl.],
Created,
Domain,
[Est. Compl. Date],
[Finance Relevant],
ID,
[IS&T Group],
Request#,
[Date Rqstd],
MakeDate(year([Date Rqstd])) as [Year Requested],
[Primary Contact],
Priority,
[BPOC Relevant],
Requestor__,
Site,
[Yr Compl. a/o Closed],
ListID,
%Key_xml_98C49F546A18E856
FROM
[Service.qvd]
(qvd)
WHERE
MakeDate(year([Date Rqstd])) >= '1/1/2014' and IsNull([Date Rqstd])=0;

[sunny_talwar · Accepted solution]

Try this:

LOAD [Assigned To],
[Bus. or Dept.],
Leaders,
Comments________________________________,
[T-Shirt Sz],
[Actual Compl. Date],
[Applic. Category],
[Request Title (Linked to SR Doc.)],
[Request Description],
[Demand Type],
Status,
[Rqstd. Compl.],
Created,
Domain,
[Est. Compl. Date],
[Finance Relevant],
ID,
[IS&T Group],
Request#,
[Date Rqstd],

'Q' & Ceil(Month([Date Rqstd])/3) as Quarter,
MakeDate(year([Date Rqstd])) as [Year Requested],
[Primary Contact],
Priority,
[BPOC Relevant],
Requestor__,
Site,
[Yr Compl. a/o Closed],
ListID,
%Key_xml_98C49F546A18E856
FROM [Service.qvd] (qvd)
WHERE Year([Date Rqstd]) >= 2014 and Len(Trim([Date Rqstd])) > 0;

and now your Quarter field should most likely not have any Q's