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

    Relative Date Colors

    Utkarsh Srivastava
    Utkarsh Srivastava Dec 5, 2018 3:29 AM

      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.

      • 114 Views
      • Categories: Dates & Times, Formatting & Display, About Tableau Products
      • Tags:
        • 1. Re: Relative Date Colors
          Utkarsh Srivastava
          Utkarsh Srivastava Dec 5, 2018 3:33 AM (in response to Utkarsh Srivastava)

          Data and workbook attached.

          • 2. Re: Relative Date Colors
            Lucie Brett
            Lucie Brett Dec 5, 2018 3:52 AM (in response to Utkarsh Srivastava)

            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
              Lucie Brett
              Lucie Brett Dec 5, 2018 3:53 AM (in response to Utkarsh Srivastava)

              Sorry, workbook attached.

              • 4. Re: Relative Date Colors
                Mahfooj Khan
                Mahfooj Khan Dec 5, 2018 4:05 AM (in response to Utkarsh Srivastava)

                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
                  Utkarsh Srivastava
                  Utkarsh Srivastava Dec 6, 2018 8:42 AM (in response to Mahfooj Khan)

                  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
                    Utkarsh Srivastava
                    Utkarsh Srivastava Dec 7, 2018 3:18 AM (in response to Lucie Brett)

                    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
                      Utkarsh Srivastava
                      Utkarsh Srivastava Dec 7, 2018 3:19 AM (in response to Mahfooj Khan)

                      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
                        Lucie Brett
                        Lucie Brett Dec 18, 2018 1:55 AM (in response to Utkarsh Srivastava)

                        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!