topic Re: Convert date field to Q1 Q2... in QlikView

Convert date field to Q1 Q2...

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Convert date field to Q1 Q2...

sunny_talwar — Thu, 09 Jun 2016 20:29:55 GMT

May be try this:

If(Len(Trim([Date Rqstd])) > 0, 'Q' & Ceil(Month([Date Rqstd])/3)) as Quarter

Re: Convert date field to Q1 Q2...

Thu, 09 Jun 2016 20:41:01 GMT

it is complaining "As Quarter" error Garbage

Re: Convert date field to Q1 Q2...

sunny_talwar — Thu, 09 Jun 2016 21:01:27 GMT

Can you share the script?

Re: Convert date field to Q1 Q2...

pho3nix90 — Fri, 10 Jun 2016 07:50:52 GMT

The must be something that isn't a proper date. If you want a workaround then you will be adding overhead into the calculation, rather sort the calendar out first in the ETL process.

Try this and see if it works.

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

Also have a look when selecting Q, which dates it links to.

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 12:24:30 GMT

Hi,

here is the script

If(Len(Trim([Date Rqstd])) > 0, 'Q' & Ceil(Month([Date Rqstd])/3)) as Quarter

error message: Garbage after expression: "as"

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 12:25:20 GMT

it links to no date, as it is Gray

Re: Convert date field to Q1 Q2...

sunny_talwar — Fri, 10 Jun 2016 14:50:36 GMT

Did you miss a comma at the end?

If(Len(Trim([Date Rqstd])) > 0, 'Q' & Ceil(Month([Date Rqstd])/3)) as Quarter,

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 15:01:10 GMT

No, I had the comma there.

Re: Convert date field to Q1 Q2...

sunny_talwar — Fri, 10 Jun 2016 15:08:23 GMT

Can you share the script for the whole table and a screenshot of the error you are getting?

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 15:22:13 GMT

Hi,

try this,

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

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 17:58:35 GMT

it woks without including (as Quarter)

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 17:59:06 GMT

it works without including (as Quarter)

Re: Convert date field to Q1 Q2...

pho3nix90 — Fri, 10 Jun 2016 18:33:28 GMT

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

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

Re: Convert date field to Q1 Q2...

sunny_talwar — Fri, 10 Jun 2016 18:47:54 GMT

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

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 18:59:32 GMT

hi,

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

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 19:02:53 GMT

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;

Re: Convert date field to Q1 Q2...

Fri, 10 Jun 2016 19:03:33 GMT

here is the script:

Re: Convert date field to Q1 Q2...

sunny_talwar — Fri, 10 Jun 2016 19:33:36 GMT

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