4 Replies Latest reply on Jun 3, 2016 12:50 PM by Winston Green

    Performing calculations at a different LOD than the view

    Winston Green

      Hi All,

       

      This is my first time posting to the forum so do let me know if i should be structuring my request differently next time. Thanks in advance

       

      I feel like this isn't a new question but no amount of searches on the forum have provided me with a complete answer.

       

      Essentially i am trying to perform a Median,Avg and Percentile calculation on the gross sales measure in the data set. The view is organized by the invoice date on the rows shelf where i have displayed the Weekday (Mon -Fri)

       

      Issue

      When performing the above stated aggregation calculations Tableau returns the values based on the 'unique" invoice lines. However, I would like to see the calculation performed at the day and clinic code level.

       

      Ex :

        

      Day 1
      Hour Clinic A Clinic B Clinic C
      9:0042012
      3:007135
      5:00896
      Total Day 1194223

       

       

      Instead of returning the median based on the Total Day 1 line ( which is 23), tableau returns the median of the unique lines (which is 8)

       

      This aggregation needs to be done for all the days in a month that correspond with the view.

       

      I have tried using LOD expresions such as; Fixed { [Dayinvoice], [ClinicCode]: Sum(GrossSales) } where DayInvoice = Datetrunc('day', Invoicedate)   But to no avail.

       

      Attached is a packaged workbook.

       

       

      Any help would be great all!

       

      Thanks again,

       

      -W

        • 1. Re: Performing calculations at a different LOD than the view
          swaroop.gantela

          Winston,

           

          Welcome to the forum. Your request was well-posed.

           

          I must apologize for not delving into all the issues at hand, but just wanted to get a clarification on your desired result.

          In the example you posted in text of your message,

          are you seeking to get the median of sums of the gross sales for the clinics for a given weekday?

           

          With the grouping that I used on Sheet 4, median of the clinic sums for the day is showing as 23 (please see below).

          But this grouping may not match your desired sheet.

           

          207885med.png

          1 of 1 people found this helpful
          • 2. Re: Performing calculations at a different LOD than the view
            Winston Green

            Hi Swaroop,

             

            Thank you for your response! After going through your twbx file I quickly realized what my first mistake was when creating the LOD expression. From what I can tell Tableau doesn't like when you mix data types meaning: Float and Integer. My sum Gross Sales is based off a "float" calculation and the LOD expression was a Integer data type. I switched both to INT.

             

            Now to answer your question: I would like to perform the aggregations at the "Day and Clinic" level with the view being Mon - Fri (invoices only occur Mon - Fri)

             

            Example:

            • There were 4 Mondays in April
            • 3 Clinics
            • Each clinic placed an order on all 4 Mondays (I don't care about the unique invoice lines - just the sum of the total order for that day)

             

            Desired Results - Tableau to take 3 clinics * 4 orders = 12 data points to determine the median for Monday sales.

             

            Side note: based on the LOD expression would tableau know to perform the aggregation on all 12 data points or would it perform the aggregation for each day  and then do a final aggregation at the view level?

             

            I.e   Median(median(day1),Median (day7), Median(day14),Median(day21)) = Median for all Mondays

            • 3. Re: Performing calculations at a different LOD than the view
              swaroop.gantela

              Winston,

               

              Apologies for the delay. Please see if the attached is closer to what you need.

               

              The Dashboard 1 of the attached shows the individual Monday sums for 3 clinics on Sheet7

              and then the Median of these 12 sums on Sheet 8.

               

              What is shown on Sheet 8 is the Median of the LOD calculation:

              { FIXED [Clinic Code],[Invoice Date]:SUM([Gross Sales])}

              which is summing by clinic and by date.

              Then I think the median is calculated over that list of 12 sums.

               

              Sheet 9 maybe has medians for the whole set for each weekday.

               

              207885full.png

              1 of 1 people found this helpful
              • 4. Re: Performing calculations at a different LOD than the view
                Winston Green

                Swaroop,

                 

                No worries, your responses are more than appreciated.

                 

                I made one little change in your LOD expression, where instead of using [invoice date] i used [dayinvoice] = datepart('day', invoicedate'). This minor change allowed tableau to calculate the median based off the 12 data points we were seeking. When I ran this calculation in the sample workbook I provided there was a minor variation between my Sql generated output.  To compound it further once I ran the calculation on the full data set the variation grew to too larger of a figure to be acceptable.

                 

                I've come to the conclusion that this is a calc better handled within Sql through a grouping statement.

                 

                Thank you again for all the support you provided it was much appreciated.

                 

                -W