A beginner question on variables

[Anonymous]

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

[YoussefBelloum]

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]

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'

[petter]

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.

[Anonymous]

Unfortunately, this doesn't work too!

[petter]

Could you share a screenshot of your data model?

[paola_valenti]

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]

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

[Anonymous]

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

[jothimala]

Hi!!

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