7 Replies Latest reply on Oct 24, 2018 2:31 PM by swaroop.gantela

    Data Formatting Issues

    Chris Glaser

      Hello, I am trying to use Tableau to create some reports. I'm having some difficulty with data. Whenever I have it used as a measure, Tableau does some weird calculation and sums it up to an amount. When I tell it that it's an attribute, the numbers show up fine. However, this restricts the kind of charts I can use.

       

      To give a more detailed overview:  we have parent companies and locations. I have each location tagged to its parent company. In this case, I'm trying to have order volume by each location. The order volume is recognized as a number in Tableau, but the default measure tries to sum it in a weird way. I've tried the other measure types and they still do not display the information correctly. When I change it to attribute, it works but this doesn't seem like an efficient workaround.

        • 1. Re: Data Formatting Issues
          swaroop.gantela

          Chris,

           

          I don't think you should have to use ATTR().

          There should be ways to change the default aggregation.

          You can also make your own aggregations using

          Level of Detail calculations:

          Overview: Level of Detail Expressions

           

          Would be grateful if you could post some fake date in

          a layout that mirrors your real source.

           

          Would also grateful for a screenshot of how your pills

          are arranged on your worksheet.

          • 2. Re: Data Formatting Issues
            Chris Glaser

            Thanks for your reply, I'll look into your link you posted. I've attached a workbook that is similar to the way that I have things formatted. I've attached some screens too.

             

            • 3. Re: Data Formatting Issues
              swaroop.gantela

              Chris,

               

              Thank you for the dataset and screenshot.

               

              Apologies if what follows is ground you have already covered,

              or is already known, or is completely off base.

               

              I couldn't tell from the screenshot if any of the tables were being joined.

              But if so, that could cause some disparate inflations in the values.

               

              If "Order Volume" is being joined/blended with AR, then the values

              will get multiplied by the number of locations in the branch (4x for A, 2x for D).

               

              Each copy will be of the same amount, so a function like ATTR will reduce it back to the original.

              Likewise will the aggregations MIN, MAX, or AVG (by pill rightclick>Measure>MIN, etc.),

              which all should be more amenable for your graphs.

               

              For more complicated situations, you could write a Level of Detail calculated field of

              { FIXED [Branch (AR)],[Month]:MIN([31-60])}

              but this would need to be done separately for each measure.

              (alternatively, your measures could potentially all be pivoted, but that's separate discussion,

              see sheet 'pivot').

               

              Please see screenshot below and workbook v10.3 attached in the Forum Thread

              Data Formatting Issues

               

              285514join.png

              • 4. Re: Data Formatting Issues
                Chris Glaser

                Those grabs were from two different sheets. I don't think I did any Joins unless if they happen automatically, but the "Affiliate (Consolidated)" is from the same place all of the other AR information was pulled from. I had done some tinkering and noticed that those measure classifications seemed to be behaving as I wanted, it just didn't really make sense to me.

                 

                Is there possibly a better way for me to organize this information in my source Excel workbook?

                • 5. Re: Data Formatting Issues
                  swaroop.gantela

                  Chris,

                   

                  Hmm. As you saw in the workbook, I made attempts at many types of aggregations,

                  but was unable to get values different from the Excel. The join was the only way I could

                  get different numbers.

                   

                  Way long shot, but potentially, a blend of data could happened unawares (unintentionally pulling in a field

                  with similar name from the other datasource), but you would see the color band on the dimensions/measures pane.

                   

                  Apologies, I'm low on ideas.  I think the way the data are organized seems reasonable.

                   

                  Are you able to recreate the problem in my workbook previously attached?

                   

                  Another option is anonymizing your workbook:

                  Anonymize your Tableau Package Data for Sharing

                  • 6. Re: Data Formatting Issues
                    Chris Glaser

                    Oh I completely misunderstood. I was using joins. Would my workaround just to use the same data source several times over to avoid the join issue?

                    • 7. Re: Data Formatting Issues
                      swaroop.gantela

                      Chris,

                       

                      Joins are not a show-stopper and may be necessary in some cases.

                      If you are in control of your datasources and can add in the needed

                      fields to avoid the join, then that will probably be easiest.

                       

                      Even with joins, taking a MIN or MAX of the field should generally

                      reduce it back down the desired value.

                       

                      If not, please post a screenshot of your datasource tab with

                      the table join diagram.