Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

itamarchubb
New Contributor III

A beginner question on variables

Hi,

I know that this is a beginner question but I need you help.

I have data set with a date column.

I would like to add two slicers to the Pivot Table I created:

  1. Year
  2. Month

How do I do it?

Best regards

9 Replies
YoussefBelloum
Esteemed Contributor

Re: A beginner question on variables

Hi,

On the script or on the front-end, you can create derived fields (Year field and Month field) using your Date field.

Example:

if your Date field is on this format: DD/MM/YYYY

Date(FIELD,'YYYY') as YEAR

Date(FIELD,'MM') as MONTH

or

Date(Date#(FIELD,'DD/MM/YYYY'),'YYYY') as YEAR

Date(Date#(FIELD,'DD/MM/YYYY'),'MM') as MONTH

dwforest
Valued Contributor

Re: A beginner question on variables

Is Date_Reviewed formatted to be a date? it appears to be non-standard format, so may be a string?

Check that your TimestampFormat in your script is set to 'DD-MMM-YY HH.mm.ss'

MVP
MVP

Re: A beginner question on variables

The two new calculated fields need to be the part that they are supposed to be - namely that YEAR becomes a year and not a full date and MONTH becomes a month and not a full date:

      .....

      Year( DATE_REVIEW_ASSIGNED ) AS YEAR_REVIEW_ASSIGNED,

      Month( DATE_REVIEW_ASSIGNED ) AS MONTH_REVIEW_ASSIGNED,

     .....

Only in that way they can be used as grouping mechanisms to calculate something for the entire year or month or year by month and so forth.

So what you have in your script screenshot from lines 156 to 157 is correct. Mind you that the v prefix you put on the resulting field names is what you normally only would use with variables - as a coding standard. Don't confuse fields with variables as they are two entirely different things. What you are dealing with here is purely fields - not variables.

itamarchubb
New Contributor III

Re: A beginner question on variables

Unfortunately, this doesn't work too!

MVP
MVP

Re: A beginner question on variables

Could you share a screenshot of your data model?

paola_valenti
New Contributor III

Re: A beginner question on variables

It appears to me that DATE_REVIEW_ASSIGNED is not a date, but a text.

If so, you coud try this in the script:

Date(Date#(DATE_REVIEW_ASSIGNED,'DD-MMM-YYYY hh.mm.ss')) as DATE_REVIEW_ASSIGNED


then you can apply month() and year() functions in the script (same LOAD istruction):

year(Date#(DATE_REVIEW_ASSIGNED,'DD-MMM-YYYY hh.mm.ss')) as vYEAR

month(Date#(DATE_REVIEW_ASSIGNED,'DD-MMM-YYYY hh.mm.ss')) as vMONTH


Else, after you have loaded in the script DATE_REVIEW_ASSIGNED as a date, you can operate in front end with year() and month() function:

year(DATE_REVIEW_ASSIGNED)

month(DATE_REVIEW_ASSIGNED)


gianpaolo_31
New Contributor III

Re: A beginner question on variables

Try this:

Year(Date(Floor(DATE_REVIEW_ASSIGNED),'DD-MM-YYYY')) as YEAR_DATE_REVIEW_ASSIGNED,

Month(Date(Floor(DATE_REVIEW_ASSIGNED),'DD-MM-YYYY')) as MONTH_DATE_REVIEW_ASSIGNED

itamarchubb
New Contributor III

Re: A beginner question on variables

Thanks everybody, I solved the problem by define the field format as date and now I can use the formula 'year()' .

jothimala
New Contributor II

Re: A beginner question on variables

Hi!!

Add new measure to the pivot table , year(date field) and month(date field)