Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!
Can you share your script for where you are trying to create this Quarter Field?
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.
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?
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
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.
Sunny - please see my latest reply. Thanks!
In what table you have the date field?
"T_PY_DTL_PRST"
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!