5 Replies Latest reply: Dec 18, 2017 11:28 PM by Anibal Martinez-Sistac RSS

    Create a new dimension based on date dimension categories

    Anibal Martinez-Sistac

      Hi all,

       

      I am trying to create a new dimension variable in Qlik Sense based on the values of a current dimension of my data set called 'Date' with no success. The idea is assignating the categories 'Previous_Period', "Promotion_Period", and "Later_Period" depending if Date falls into one of the 3 periods defined manually by the user in 'Data Load Editor'.

       

      I have a 'Date' variable in my dataset with the following format: 'DD.MM.YY'. I have created a new variable in the 'Data Load Editor' called VTimePeriod and initialized it with the value 'Other'. I have also created 6 new variables with specific dates to set the intervals of 'Previous_Period', 'Promotion_Period', and 'Later_Period'.

       

      My code has the following look:

       

      if (

              (Date > vStartDatePrev and Date < vEndDatePrev)

              ,

            vTimePeriod = 'Previous_Period',

      if (

              (Date > vStartDatePromo and Date < vEndDatePromo)

              ,

            vTimePeriod = 'Promo_Period',     

      if (

              (Date > vStartDateAfter and Date < vEndDateAfter)

              ,

            vTimePeriod = 'Later_Period',

            'Other_Period'

          )

        )

      )

       

      Nevertheless, when I try to create a Barplot it doesn't work well as it only shows 'Other_Period'. I am attaching the images of outputs. It seems that my conditions do not have the right syntax. I have created in the past this visualization, but hardcoding with Match() function. I want to do the same in a generic approach. Thank you sincerely for all your help.

       

      Output Sample.png

      Data Load Editor.png

        • Re: Create a new dimension based on date dimension categories
          Luis Madriz

          Hi,

           

          Have you tried replacing vStartDatePrev  for $(vStartDatePrev), and the others?

            • Re: Create a new dimension based on date dimension categories
              Luis Madriz

              And also, instead of assigning a value to variable like vTimePeriod = 'Previous_Period', just use 'Previous_Period'


              I hope this helps,


              Cheers,


              Luis

              • Re: Create a new dimension based on date dimension categories
                Anibal Martinez-Sistac

                Thank you very much for your reply Luis. I have implemented your changes. The Fx editor of the variables shows now the message "OK" after your suggestions. Nevertheless, the visualization output is still not working. Now my Fx field for the new variable vTimePeriod is:

                 

                if (

                        (Date > $(vStartDatePrev) and Date < $(vEndDatePrev))

                        ,

                      'Previous_Period',

                if (

                        (Date > $(vStartDatePromo) and Date < $(vEndDatePromo))

                        ,

                      'Promo_Period',   

                if (

                        (Date > $(vStartDateAfter) and Date < $(vEndDateAfter))

                        ,

                      'Later_Period',

                      'Other_Period'

                    )

                   )

                  )

                 

                I have checked that there is no problem with the dates and that they are correctly formatted. If I use Today(Date) it gives the correct current date in the specified format: "DD.MM.YY".

                 

                Although the visualization continues giving an error message. I will attach my screen captions. Thanks in advance for your help.

                Invalid graph.png

                Variable Expression.png

                Variable Settings.png

                  • Re: Create a new dimension based on date dimension categories
                    Luis Madriz

                    Hi Anibal,

                     

                    I just tried the following configuration and it works for me:

                    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 CollationLocale='en-US';

                    SET DateFormat='DD.MM.YY';

                    SET TimestampFormat='DD.MM.YY h:mm:ss[.fff] TT';

                    SET CollationLocale='de-DE';

                    SET FirstWeekDay=6;

                    SET BrokenWeeks=1;

                    SET ReferenceDay=0;

                    SET FirstMonthOfYear=1;

                    SET CreateSearchIndexOnReload=1;

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

                    SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

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

                    SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                     

                    Temp1:

                    Load * inline [

                    Date0

                    05.09.17

                    06.09.17

                    07.09.17

                    08.09.17

                    09.09.17

                    10.09.17];

                     

                    SET vStartDatePrev=05.09.17;

                    SET vEndtDatePrev=06.09.17;

                    SET vStartDatePromo=07.09.17;

                    SET vEndtDatePromo=08.09.17;

                    SET vStartDateAfter=09.09.17;

                    SET vEndDateAfter=10.09.17;

                     

                    And this is the Dimension I created:

                    =if (

                            (Date0 >= vStartDatePrev and Date0 <= vEndtDatePrev)

                            ,

                          'Previous_Period',

                    if (

                            (Date0 >= vStartDatePromo and Date0 <= vEndtDatePromo)

                            ,

                          'Promo_Period',  

                    if (

                            (Date0 >= vStartDateAfter and Date0 <= vEndDateAfter)

                            ,

                          'Later_Period',

                          'Other_Period'

                        )

                       )

                      )

                     

                    Untitled.png

                     

                    So everything you did originally was correct except assign vTimePeriod a value.

                     

                    Please try and let me know,

                     

                    Cheers,

                     

                    Luis

                      • Re: Create a new dimension based on date dimension categories
                        Anibal Martinez-Sistac

                        Thank you so much for your answer Luis. Your answer is correct.

                         

                        There were 3 mistakes in my Qlik code:

                         

                        1) I did not add the Load * inline section of your code in my 'Data Load Editor'.

                        2) I inititalized the vTimePeriod variable in the 'Data Load Editor' and it was not necessary as you pointed.

                        3) It is not necessary to add the "$" characters to the variables in the Creation of the new Dimension.

                         

                        It works now perfect. Thanks a lot:

                         

                        Perfect_Graph_After_Modification.png

                         

                        P.D. I will try to do it now in the most generic way possible. Without hardcoding the Load * inline part as well, if possible. Thanks.