Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gfisch13
Creator II
Creator II

Selecting a quarter

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.

Quarterfield.PNG

THANKS!

1 Solution

Accepted Solutions
miguelbraga
Specialist III
Specialist III

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!

View solution in original post

10 Replies
sunny_talwar

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

jolivares
Specialist
Specialist

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.

gfisch13
Creator II
Creator II
Author

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?

miguelbraga
Specialist III
Specialist III

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,

MB

gfisch13
Creator II
Creator II
Author

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";

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_EMPE_STG"

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

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_PO_DTL_PRST";

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_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.

gfisch13
Creator II
Creator II
Author

Sunny - please see my latest reply.  Thanks!

miguelbraga
Specialist III
Specialist III

In what table you have the date field?

gfisch13
Creator II
Creator II
Author

"T_PY_DTL_PRST"

miguelbraga
Specialist III
Specialist III

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!