3 Replies Latest reply on Aug 12, 2016 11:05 AM by Stoyko Kostov

    Compare a particular Quarter Sales with the average of other quarters sales.

    Smeet Patel

      I have a task to compare Sales of a particular quarter of any year ex:Q1 of 2015 with the rest of the quarter's average i.e average of Q2,Q3&Q4 of 2015.

      The user should be able to control/filter the quarter and year.

      Have been trying various methods but somehow I am not able to solve it.

       

        • 1. Re: Compare a particular Quarter Sales with the average of other quarters sales.
          Stoyko Kostov

          Hi Smeet,

           

          You can use a level-of-detail expression (LOD) to help you with that.

           

          Define a calculated field with the following formula: ({FIXED:SUM(Sales)}-[Sales])/({FIXED:COUNT(Quarter)}-1)

           

          The FIXED LODs instruct Tableau to compute the average ignoring other fields on which your current view may be partitioned.

           

          I also subtract Sales from the SUM, so that the average is computed over all other quarters except the current one. For the same reason I subtract 1 from COUNT.

           

          Then drag Quarter to columns, Sales to Text and the newly created calculated field to Text. This will give you the comparison you need.

           

          I'm attaching a sample workbook that I prepared to demo the above. Let me know if you have other questions.

          • 2. Re: Compare a particular Quarter Sales with the average of other quarters sales.
            Smeet Patel

            https://public.tableau.com/static/images/Av/AverageSalesQuarterly_Smeet/Sheet1/1.png

            https://public.tableau.com/views/AverageSalesQuarterly_Smeet/Sheet1?:embed=y&:display_count=yes

            Hi Stoyko,

             

            Thanks for the help.

            I am using the  "Sample - Superstore Subset (Excel)_0"  dataset.

            Your solution works fine,but when I tried implementing,it is showing a mismatch in data.

            I am attaching the workbook.

            I know there is some silly mistake from my end but please help me to figure it out.

            Appreciate your efforts in advance.

             

            Quarter of Order DateYear of Order DateSales
            Q12010415,880.26
            Q22010352,786.14
            Q32010456,684.80
            Q42010698,981.68

             

            If I select Q1 then the "Average of Sales Except the filtered Quarter" should be (SUM(Q1+Q2+Q3)/3) i.e (1,508,452.62/3)=

            502817.54.

            But in my worksheet it is showing as 488,156.69.

            LOD is:-

            ({FIXED:SUM(Sales)}-[Sales])/({FIXED:COUNT([Order Date (Quarters)])}-1).

            Please let me know if any information is missing.

             

            Thanks and Regards,

            Smeet

            • 3. Re: Compare a particular Quarter Sales with the average of other quarters sales.
              Stoyko Kostov

              Hi Smeet,

               

              2 things:

               

              1. The way I understood your question, you needed the average of all quarters except the one selected. I.e. if you select Q1, you need (Q2+Q3+Q4)/3. I don't know why you were expecting to see (Q1+Q2+Q3)/3, but if this is indeed what you need, please explain by what criteria you want to exclude Q4 when you select Q1, and I'll try to come up with a formula.

               

              2. I didn't know you had a finer granularity of quarterly sales. Because of that, use the following formula:

              ({EXCLUDE[Order Date (Quarters)]:SUM(Sales)}-{INCLUDE[Order Date (Quarters)]:SUM(Sales)})/({EXCLUDE[Order Date (Quarters)]:COUNTD([Order Date (Quarters)])}-1)

               

              EXCLUDE directs Tableau to exclude the specified field from the partitioning, giving you the overall total, and not the total within the current partition.

              INCLUDE directs it to include it to the partitioning it already uses, giving you the total within the current partition.

               

              Change your formula to that, and you'll see exactly (Q2+Q3+Q4)/3.

               

              Hope this helps - let me know if you have more questions.

              1 of 1 people found this helpful