8 Replies Latest reply on Dec 18, 2018 1:55 AM by Lucie Brett

# Relative Date Colors

Hi, I have a question around relative date colors. The data is the following: Date Number Currently, we have the last month as grey, 2nd last month as orange and 3rd last month as blue. (i.e. November - grey, October - orange, September- blue) But next month, we get data for december. I need december to be grey, November to be orange and October as blue. I can do this manually but I need an automated way to set this up.

• ###### 1. Re: Relative Date Colors

Data and workbook attached.

• ###### 2. Re: Relative Date Colors

Hi Utkarsh,

You could create a dynamic colour scheme using logic which shows whether the month is the current month (M0), last month (M-1) etc.

if [Date] >= DATETRUNC('month', today()) then 'M0'

elseif [Date] > DATEADD('month', -1, DATETRUNC('month', today()))-1

and [Date] < DATEADD('month', 0, DATETRUNC('month', today()))-1 then 'M-1'

elseif [Date] > DATEADD('month', -2, DATETRUNC('month', today()))-1

and [Date] < DATEADD('month', -1, DATETRUNC('month', today()))-1 then 'M-2'

elseif [Date] > DATEADD('month', -3, DATETRUNC('month', today()))-1

and [Date] < DATEADD('month', -2, DATETRUNC('month', today()))-1 then 'M-3'

end

Lucie

• ###### 3. Re: Relative Date Colors

Sorry, workbook attached.

• ###### 4. Re: Relative Date Colors

Hi,

You may try this

IF DATETRUNC('month',[Date])={MAX(DATETRUNC('month',[Date]))} THEN 1 //Latest Month Nov

ELSEIF DATETRUNC('month',[Date])=DATEADD('month',-1,{MAX(DATETRUNC('month',[Date]))}) THEN 2 //Latest-1 Month Oct

ELSEIF  DATETRUNC('month',[Date])=DATEADD('month',-2,{MAX(DATETRUNC('month',[Date]))}) THEN 3 //Latest-2 Month Sep

ELSE 0 //Assign color for the rest month, if you select more than three months in your view

END

Once done then make it discrete and drag it to dimension pane then put it on color marks. Finally assign colors

Note: Don't forget to put the filters on context.

Workbook v10.5 attached for your reference. Let us know if this help.

Mahfooj

• ###### 5. Re: Relative Date Colors

Hi, Thanks for your help. That works well except I need to have the month name in the data as well as I have parameter drop down for current month. If current month = Yes, then I need to have December - grey, November - orange, October- blue. IF current month = No, then I need to have November - grey, October- orange, September- blue. Any ideas. Harry

• ###### 6. Re: Relative Date Colors

Thank you Lucie. Is there anyways to set this up inspite of what values offers I ended up doing this IF DATETRUNC('month',[Date])={MAX(DATETRUNC('month',[Date]))} THEN datename('month',dateadd('month',-1,today())) //Want this grey always ELSEIF DATETRUNC('month',[Date])=DATEADD('month',-1,{MAX(DATETRUNC('month',[Date]))}) THEN datename('month',dateadd('month',-2,today())) //Want this orange always ELSEIF  DATETRUNC('month',[Created On])=DATEADD('month',-2,{MAX(DATETRUNC('month',[Created On]))}) THEN datename('month',dateadd('month',-3,today())) //Want this light blue always END

• ###### 7. Re: Relative Date Colors

Thank you Mahfooj. Is there anyways to set this up inspite of what values offers I ended up doing this IF DATETRUNC('month',[Date])={MAX(DATETRUNC('month',[Date]))} THEN datename('month',dateadd('month',-1,today())) //Want this grey always ELSEIF DATETRUNC('month',[Date])=DATEADD('month',-1,{MAX(DATETRUNC('month',[Date]))}) THEN datename('month',dateadd('month',-2,today())) //Want this orange always ELSEIF  DATETRUNC('month',[Created On])=DATEADD('month',-2,{MAX(DATETRUNC('month',[Created On]))}) THEN datename('month',dateadd('month',-3,today())) //Want this light blue always END

• ###### 8. Re: Relative Date Colors

Hello Utkarsh,

Sorry for the delay.

If you want the shading to always be the same but the label to be dynamic, why not have two calculated fields - one for shading and one for labelling? The labelling can be as above and then the shading can have the same value for each month i.e. 'current month', 'last month' etc. and then the shading won't change each time.

Hope that helps!