topic Selecting a quarter in QlikView

Selecting a quarter

gfisch13 — Wed, 21 Oct 2015 14:15:31 GMT

Hi - I've read so many posts about setting up a field for the Quarter, and have tried to implement a few that have not worked as they seem to not sync with my data.

I'm looking to setup a dynamic field for the quarter. I've already setup year and month based on a date (PY_DT) in my database, but I cannot figure out how to setup the Quarter.

This is what the field looks like (how I want it). I just cannot seem to set it up properly to work with my tables/charts. New to Qlikview and trying to keep it simple.

THANKS!

Re: Selecting a quarter

sunny_talwar — Wed, 21 Oct 2015 15:41:07 GMT

Can you share your script for where you are trying to create this Quarter Field?

Re: Selecting a quarter

jolivares — Wed, 21 Oct 2015 15:42:47 GMT

First everything begin in your data model, look if you have a Quarter field well defined, that it means all the relations with this field id ready. Last it have to work in you QV model as is.

Example:

Table_Sales:

Load Cust,

DateSales,

Year(DateSales) as Y_Sales,

'Q'&Ceil(Month(DateSales)/3) as Q_Sales,

Month(DateSales) as M_Sales,

Amount

From Sales...

In this case your date field is attached directly to your fact table data.

Re: Selecting a quarter

gfisch13 — Wed, 21 Oct 2015 15:54:39 GMT

The quarter field is not well defined at all. Using my date format I was able to easily create the expression for Year and Month. Date format is MM/DD/YYYY.

in your example above, does Q_Sales become the quarterly values that I'm looking for, or is that a variable holding those values?

Re: Selecting a quarter

miguelbraga — Wed, 21 Oct 2015 16:09:45 GMT

Hi George,

You can do as Juan have told you. The field Q_Sales will be linked to you Date field, ence linked to your Year and Month field. Have fun and good job.

Regards,

Re: Selecting a quarter

gfisch13 — Wed, 21 Oct 2015 16:45:53 GMT

Attached is my load script. I'm not understanding how to adapt the lines of code you posted with it. Does this become a separate tab?

The field PY_DT is my payment date that I'm going to adapt and it is in the last table that loads (T_PY_DTL_PRST).

I'm very new to Qlikview, sorry for the confusion.........

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET Quarter='Q1;Q2;Q3;Q4';

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=DMBA_SIU_DEV;Extended Properties="DSN=DMBA_SIU_DEV;Description=DMBA_SIU_DEV;UID=;Trusted_Connection=Yes;APP=QlikView;WSID=US1900050;DATABASE=DMBA_SIU";Initial Catalog=DMBA_SIU];

SQL SELECT *

FROM "DMBA_SIU".dbo."T_EXP_PRST";

SQL SELECT *

FROM "DMBA_SIU".dbo."T_EMPE_STG"

WHERE CO_CD in ('MLI','MLG','MPC');

SQL SELECT *

FROM "DMBA_SIU".dbo."T_PO_DTL_PRST";

SQL SELECT *

FROM "DMBA_SIU".dbo."T_PY_DTL_PRST";

Juan Olivares wrote:

First everything begin in your data model, look if you have a Quarter field well defined, that it means all the relations with this field id ready. Last it have to work in you QV model as is.

Example:

Table_Sales:

Load Cust,

DateSales,

Year(DateSales) as Y_Sales,

'Q'&Ceil(Month(DateSales)/3) as Q_Sales,

Month(DateSales) as M_Sales,

Amount

From Sales...

In this case your date field is attached directly to your fact table data.

Re: Selecting a quarter

gfisch13 — Wed, 21 Oct 2015 16:46:53 GMT

Sunny - please see my latest reply. Thanks!

Re: Selecting a quarter

miguelbraga — Wed, 21 Oct 2015 16:48:44 GMT

In what table you have the date field?

Re: Selecting a quarter

gfisch13 — Wed, 21 Oct 2015 16:49:18 GMT

"T_PY_DTL_PRST"

Re: Selecting a quarter

miguelbraga — Wed, 21 Oct 2015 16:52:48 GMT

If so do this:

T_PY_DTL_PRST_Final:

LOAD *,

'Q'&Ceil(Month(DateSales)/3) as Quarters

Resident T_PY_DTL_PRST;

DROP Table T_PY_DTL_PRST;

And please comment or delete this: [SET Quarter='Q1;Q2;Q3;Q4';]

NOTE: change DateSales to your Date Field!

Re: Selecting a quarter

gfisch13 — Wed, 21 Oct 2015 17:03:23 GMT

Thank you - this worked, but jsut for my education........my creating T_PY_DTL_PRST_Final, did the QUARTERS field now become part of that table, negating the need for T_PY_DTL_PRST??

Appreciate the help!