1 Reply Latest reply on Jul 18, 2016 11:23 AM by Naveen Agarwal

    Chart to compare sales across quarters

    Rajarshi Ghose Dastidar

      Hi,

      I'm trying to create a chart to show actual, forecast and target sales. My data is in below format:

       

        

      Product CategoryQ1Q2Q3Q4SalesQuarterType
      A 100Q1Actual
      B 50Q1Actual
      C 70Q2Actual
      D 120Q2Actual
      A30 Target
      B2010 70 Target
      C 30 Target
      D60550 Target
      A Target
      B 80 Forecast
      C50 60 Forecast
      D Forecast
      A 40 Forecast


      When Type = "Actual", we compute actual sales. The sales value is the sum of the column Sales and we look at it across quarters (Q1 sum of sales, Q2 sum of sales).

      When the Type is "Target" we compute the sum of values under Q1, Q2, Q3, Q4. These would give me the target sales values across each quarter.

      In a similar way, when Type is "Forecast" we we compute the sum of values under Q1, Q2, Q3, Q4. These would give me the forecast sales values across each quarter.

      (Note: When ever the "Type" is Actual, the values under Q1, Q2, Q3, Q4 are blank).

      In Tableau, I want to have a single chart, which can show me for each quarter what is my actual, target and forecast sales. Preferably, I would like to have my actual sales in bar chart and the target and forecast sales in line chart.

       

      Can someone help me solve this? I am not sure how I can segment the sales values across quarters for the three categories (actual, target & forecast) to show in a chart since the data is in a very different format. What would be a good calculated field equation?

        • 1. Re: Chart to compare sales across quarters
          Naveen Agarwal

          I think you don't need the "Sales" column. Also you don't need individual columns for each Quarter. You can arrange your data under the following Dimensions:

           

          Product Type (A, B, C,D)

          Type (this would have values Actual, Target and Forecast)

          Quarter (this would have values Q1, Q2, Q3, Q4)

           

          And a single Measure:

           

          Sales (this will contain the values, irrespective of Actual, Target and Forecast)

           

          Then you can easily create the view you are looking for.