3 Replies Latest reply on Jan 13, 2019 8:19 PM by Richard Michaels

    HELP - Pivoted data causing ISSUES - 2018.2

    Richard Michaels

      Hi All,

      I hope this message finds everyone well.

      I need some assistance and have been racking my mind with this problem.

      One the attached, I have an Order Intake field. This field's value is grossly overstated because I pivoted some data (36 columns to be exact) from my Excel sheet as I had date fields in my columns.

      This total should equal 4,440,137 not what is currently presented. I believe this has to do with Pivoting those 36 columns. Outside of just dividing my measure by 36, are there any other ideas in order to resolve this pivoting issue?

       

      Cheer!

        • 1. Re: HELP - Pivoted data causing ISSUES - 2018.2
          Ken Flerlage

          Yes, the pivoting is causing those numbers to be duplicated multiple times. You'll probably want to use a FIXED LOD (see FIXED Level of Detail Expressions - Tableau ) to correct this problem. Are you familiar with LODs? If not, please read the above.

           

          For this to work, we'll need to better understand the data you're working with. Is there some sort of set of key fields to which the order amount ties. For example, I was looking for an order number or something like that? We need that key (could be multiple fields) so we can fix on that in the LOD.

          • 2. Re: HELP - Pivoted data causing ISSUES - 2018.2
            Ken Flerlage

            Here's an example. If you were to tell me that each row can be uniquely identified by Sales Document and PLM Product ID (i.e. there is only ever 1 record in your original data set for each combination of these two fields), then we could create an LOD like this. We're telling it to fix on those two fields, then bringing back the max of the Euro Conversion.

             

            Euro Total

            // Use a fixed LOD to get the total, removing duplicate records.

            {FIXED [Sales document],[PLM Product ID]: MAX([EURO Conversion])}

             

            Then, when we sum this, Tableau will no longer sum up all the extra records--it'll only sum each unique pair of these once, which results in a total of 4,690,630.

             

            This total doesn't match what you're expecting, so I'm going to assume that these two fields do not necessarily identify a unique line, but if you have a field or set of fields that do, you could just replace mine with those in the LOD. If you do not have a field that uniquely identifies each row, that will be a problem as we won't have anything to fix on. You may need to artificially create a row ID or something like that in your source.

            • 3. Re: HELP - Pivoted data causing ISSUES - 2018.2
              Richard Michaels

              This is excellent, thanks!