4 Replies Latest reply on Dec 27, 2018 6:33 AM by jagz

    Current vs Previous Quarter Calculation

    jagz

      I am trying to find the difference between the current quarter and the previous quarter, and show it in a column next to the average. I believe this can be done using a calculation, but we are a bit stumped. We are fairly new users to Tableau and have been using it intensively at work recently. We are still learning and this forum has been an invaluable resource. If any of the data needs fixed please let me know. We had to trim the data and make it anonymous. I have attached a pre-packaged workbook and outlined what we are struggling with below.

       

      • I am trying to find the difference between the current quarter and the most previous quarter as a whole number (NOT a percentage) in a column next the Average.
      • Filer to show only the top 3 highest orders for the most recent quarter. This data is updated once/week, so the filter needs to be able to adjust as the data changes.
      • Change the “total” column header to “average” (not that important at the moment)

      QvQ_Community_Question.png

       

      Thank you for your help.

        • 1. Re: Current vs Previous Quarter Calculation
          Norbert Maijoor

          Hi Jag,

           

          Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

           

           

          1. D1. Display: DATEDIFF('quarter',[Order Date],today())<=1 and DATEDIFF('quarter',[Order Date],today())>=0

           

          2. M1. Previous Quarter: {fixed[Account Rep],[Contact]:sum(if DATEDIFF('quarter',[Order Date],today())=1 then [Items Ordered] end)}

           

          3. M2. Current Quarter: {fixed[Account Rep],[Contact]:sum(if DATEDIFF('quarter',[Order Date],today())=0 then [Items Ordered] end)}

           

          4. M3. Delta: zn([M2. Current Quarter ])-zn([M1. Previous Quarter])

           

          5. M4. Display:

          if size()<>1 then zn(sum(([Items Ordered])))

          elseif size()=1 then sum([M3. Delta])

          END

           

          6. Drag the required objects to the indicated locations and filter  D1. Display on TRUE

           

          Display TRUE.png

           

          7. Filter Contact on Top 3 by M2. Current Quarter

           

          Hope it helps,

           

          Regards,

          Norbert

          • 2. Re: Current vs Previous Quarter Calculation
            jagz

            Thank you, Norbert.

             

            I can't quite seem to get the calculation to work. I believe is because the SUM(Items Ordered) is actually a COUNT/COUNTDISTINCT on the data set that we are using, with a custom filter that is the equivalent of:

             

            if [Order Date] =

            {fixed [Items Ordered]:Min([Order Date])}

            then 1 else 0 end

             

             

            With the "Items Ordered" being a count distinct, not a SUM in my particular case.  This filter has to be used to ensure that each "items ordered" is counted only once per "order date", to ensure that multiple items ordered aren't counted on the same day. Is there any workaround for this? I am happy to provide more information if this is too vague.

             

            Thank you SO much for your help.

            • 3. Re: Current vs Previous Quarter Calculation
              Norbert Maijoor

              Hi Jagz,

               

              Are you able to share your workbook in .tbwx-format?

               

              Regards,

              Norbert

              • 4. Re: Current vs Previous Quarter Calculation
                jagz

                Norbert,

                 

                Are you referring to the original workbook? My sample workbook that I attached in the original post is in .tbwx-format. I am not able to attach the original workbook I am referring to as it contains PHI. I have been trying to wrap my head around how I could make an example workbook with a better representation of our count distinct issue, but I haven't had any luck. Please let me know if I can do anything else to help.

                 

                Thanks,

                Ryan