Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create a new dimension based on date dimension categories

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

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

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

View solution in original post

5 Replies
luismadriz
Specialist
Specialist

Hi,

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

luismadriz
Specialist
Specialist

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


I hope this helps,


Cheers,


Luis

Anonymous
Not applicable
Author

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

luismadriz
Specialist
Specialist

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

Anonymous
Not applicable
Author

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.