10 Replies Latest reply: Oct 21, 2015 1:03 PM by George Fischetti RSS

    Selecting a quarter

    George Fischetti

      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!

        • Re: Selecting a quarter
          Sunny Talwar

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

          • Re: Selecting a quarter
            Juan Olivares

            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
                George Fischetti

                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
                  George Fischetti

                  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.