3 Replies Latest reply on Jun 26, 2018 3:06 AM by Matthijs Blenkers

    Coloring Area Chart Red or Green Based on Month Parameter

    youthesh sagar

      I have two Cal cautions created which changes based on the year parameter selected.

      1) Current year sales

      sum(if YEAR([Order Date])=[Year] and

      month([Order Date])<=[Month]

      then [Sales] end)

       

       

      2) Previous year sales

      sum(if YEAR([Order Date])=[Year]-1

      then [Sales] end)

       

      I have a dual axis chart where line chart shows Previous year sales and Area chart shoes current year sales up to the latest month selected from Month Parameter.

       

      I want to create a calculation which satisfy below logic

       

      Logic: Area chart should be Colored Red if Current Year sales -Previous Year sales <0 else Green.

       

      Eg:  When User selects 2016 and May in parameter. Then Current Year- Previous Year is

      44261-56691=-12430(Which is less than Zero. Hence the area chart should be Red else Green)

       

      Eg:  When User selects 2016 and Jun in parameter. Then Current Year- Previous Year is

      52982-39430=+13552(Which is Greater than Zero. Hence the area chart should be Green else Red)

       

       

      The Coloring should be dynamic based on the month selected.

       

      Can anyone please help me in resolving this problem. Have attached the workbook.

      Thank You

        • 1. Re: Coloring Area Chart Red or Green Based on Month Parameter
          Mavis Liu

          HI Youthesh,

           

          Please use this calculation:

           

          if

          { EXCLUDE DATEPART('month', [Order Date]) : (

          sum(if YEAR([Order Date])=[Year] and

          month([Order Date])=[Month]

          then [Sales] end)

          -sum(if YEAR([Order Date])=[Year]-1

          and month([Order Date])=[Month]

          then [Sales] end) <=0)}

          then 'Red'

          else 'Green'

          END

           

          and place it onto colour.

           

           

          2018-06-26_10h58_52.png

           

           

          2018-06-26_10h59_05.png

           

          Thanks,

           

          Mavis

          • 2. Re: Coloring Area Chart Red or Green Based on Month Parameter
            Mahfooj Khan

            Hi

             

            another way of achieving this is

            Create two calculated fields. I just simplify the calculations using your already created fields and enclosed with LOD{}.

             

            Current Month Sales:

            {sum(if YEAR([Order Date])=[Year] and

            month([Order Date])=[Month]

            then [Sales] end)}

             

            Previous Year Same Month Sales:

            {sum(if YEAR([Order Date])=[Year]-1 AND MONTH([Order Date])=[Month]

            then [Sales] end)}

             

            color:

            if [Current Month Sales]<[Previous Year Same Month Sales] then 'Red' ELSE'green'

            END

             

            Use this color field on your current year sales marks card.

            See the screenshots

             

            Hope this help.

             

            Mahfooj

            • 3. Re: Coloring Area Chart Red or Green Based on Month Parameter
              Matthijs Blenkers

              Hi,

               

              I'm not sure what you're trying to show with the viz.

              It looks like you want the color of the WHOLE area-chart to be determined by the comparison of a specific month (based on a parameter). But based on the lay-out of the viz Tableau has to show each month separate. Hence, the calculation and coloring is going bonkers :-)

               

              You can do a couple of things

              1. Use an area-chart in combination with a rolling total (so show YTD results based on parameter)

              2. Use another viz like bar in bar to show the results and color each month

               

              It all depends on what you want to tell with this viz.

               

              gr.

              Matthijs