4 Replies Latest reply on Jun 11, 2013 12:54 PM by sira.sasitorn

    Summarizing at different granularity

    sira.sasitorn

      Hi,

       

      I'm trying to calculate measures based on the same formula but summarized at different levels. For example,

       

      StateSalesZipValue 1Value 2Rate (Value 2/ Value1)Statewide Rate
      ILSales 1606011020.40.37
      ILSales 2606011580.40.37
      ILSales 360090510.20.37

       

      The bolded column headers are the ones I want to calculate. Rate and Statewide Rate are calculated using the same formula, which is [Value 1 / Value 2]. However, it is summed up at different value.

       

      Rate is being summarized at Zip level. So (8+2) / (10+15). Leaving out the 3rd row since it is from another zip.

       

      Statewide rate is being summarized at state level so it is going to sum up all Value 1 and Value 2. so (2+8+1) / (10+15+5).

       

      The table above is just to explain the logic, I would like to build a line graph based on Rate and Statewide Rate (to compare). I tried using running_avg but the numbers are still wrong. Is there another way to approach this?

       

      Thank you!

        • 1. Re: Summarizing at different granularity
          Mark Holtz

          Hi Sira,

           

          You were close. You want WINDOW_SUM(SUM([Value1])) / WINDOW_SUM(SUM([Value2])).

          The key is creating 2 fields with that as the calculation, then setting them to the appropriate different table calculation settings.

           

          For the STATE RATE field:

          Computes using: Advanced >> State, Zip and Sales (the order matters)

          At the level: Sales

          Restarting every: State

           

          For the ZIP RATE field:

          Computes using: Advanced >> State, Zip and Sales (the order matters)

          At the level: Sales

          Restarting every: ZIP

           

          Does that help?

          1 of 1 people found this helpful
          • 2. Re: Re: Summarizing at different granularity
            sira.sasitorn

            Thank you for your reply Mark Holtz!

             

            You answered my question. I have an additional question. I'm trying to make a graph where I compare STATE RATE to ZIP RATE to see how the ZIP is doing compare to STATE, so I will be having ZIP as my global filter. In the picture below, the bar chart is Value 1, and the lines are STATE RATE and ZIP RATE. The X axis is the date field. If I have ZIP as a global filter, STATE RATE will be filtered and have the same value as ZIP RATE right? I couldn't find a way around this. Thanks again for your help.

             

            Test.JPG

            • 3. Re: Re: Summarizing at different granularity
              Mark Holtz

              Hi Sira,

               

              Small world! Another forum post I was responding to was essentially after the same thing. "ZIP" is really a sub-classification of "STATE". You might find this thread helpful: http://community.tableau.com/message/214173?et=watches.email.thread#214173

               

              The issue that complicates things here is that if you try to filter to a specific ZIP code, you are tossing out all of the records in that same state for the other ZIP codes, so I don't think you can get the STATE total you want--at least not with a single data source.

               

              The simplest solution, I think is to use a 2nd (duplicate) data source to do your STATE level metric. You would link the two data sources on Date and build the table calculation the same way in the 2nd source.

               

              Also, this link to the knowledge base describes the process: Showing Summary and Detail Together

              • 4. Re: Summarizing at different granularity
                sira.sasitorn

                Hi Mark,

                 

                Thank you for your advice. I tried having 2 data sources. One at ZIP level, and the other at STATE level. Then I build the graph on the ZIP level data source and drag in Statewide Rate (that's computed on STATE level data source) field into the graph to compare (as in the picture above).

                 

                The problem is, The global filters in ZIP doesn't filter the Statewide Rate too. Eventhough the statewide rate is in the same worksheet. As a result, Zip rate will be correct, but the Statewide rate will be wrong since it will ignore the filter such as months and aggregate everything.

                 

                Is there a way to carry the filter over? I tried using parameters but it doesn't work since because both fields from different data source are in the same worksheet.

                 

                Any help is very much appreciated.

                 

                Thanks again!