7 Replies Latest reply on Dec 7, 2018 3:19 AM by Utkarsh Srivastava

    Relative Date Colors

    Utkarsh Srivastava

      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
          Utkarsh Srivastava

          Data and workbook attached.

          • 2. Re: Relative Date Colors
            Lucie Brett

            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

              Sorry, workbook attached.

              • 4. Re: Relative Date Colors
                Mahfooj Khan

                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

                  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

                    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

                      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