6 Replies Latest reply on Aug 9, 2016 7:43 AM by Joshua Milligan

    Unpivot calculation without adding new data source

    Rytis Zolubas

      Hi guys,

       

      I have just started using the Tableau and I would like to do a calculation from a pivoted table. The data comes from tableau server database, so I cannot use pivot function. I have attached an example of what I have in a database.

      I wish to calculate a total sum of each resource name per day.

      Current table:

       

           

      dateResource 1Value resource 1Resource 2Value resource 2Resource 3Value resource 3
      2016-01-01Data12Data23Data52
      2016-01-01Data2Data236Data43
      2016-01-01Data2100Data32Data5
      2016-01-01Data22Data319Data65
      2016-01-01Data323Data33Data13

       

      I wish a new table in a Tableau that would look like:

       

      DateResourceTotal
      2016-01-01Data52
      2016-01-01Data13
      2016-01-01Data2141
      2016-01-01Data344
      2016-01-01Data43
      2016-01-01Data52
      2016-01-01Data65

       

      Is it possible to do it in Tableau?

       

      Thank you very much

       

      Best regards,

      Rytis

        • 1. Re: Unpivot calculation without adding new data source
          John Sobczak

          You can create a custom SQL in Tableau and combine the resource fields into a single column similar to what I describe in this thread:

           

          columns pivot from column to rows using DB

          • 2. Re: Unpivot calculation without adding new data source
            Rytis Zolubas

            Thank you for your reply John!

             

            The problem is that I have only access to Tableau Server (not directly to conventional DB). On this server I cannot do any custom SQL queries, nor pivot the table. That is why I was wondering wheather it is possible to do the calculation using calculated field.

            • 3. Re: Unpivot calculation without adding new data source
              John Sobczak

              Rytis,  Sorry I missed that part and just thought it was a database.  I am not sure that is possible via calculations. My guess is no but maybe someone else can say for sure.

              • 4. Re: Unpivot calculation without adding new data source
                Joshua Milligan

                Rytis,

                 

                Your actual case may be a bit more complicated than your example, in which case this proposed solution might not work as well as you'd like, but one possibility is to create a separate data source (in an Excel file, for example) which contains a combination of all the dates and all the resources.  Then, you could use that as the primary data source to get a view for the date and resource and then blend (3 times, to 3 copies of the data source above, for each of the Resource columns) to get the total values for each kind of resource for each date.

                 

                There are a few things that would make this solution less attractive:

                • If there are additional dimensions (other than date and resource) that need to be used (as filters or slicers)
                • If there are more than 3 resource columns (you'll have a copy of the Tableau data source for each additional column, so that gets messy rather quickly)
                • If you have some views based on the original data source and some based on this new primary data source and you want to use them together in a dashboard with shared filters (though Tableau 10 will make this a bit easier with cross data source filters)

                 

                A tip on the new data source: if you have one Excel tab with all dates and a Join column that always has a single constant value (e.g. 1) and another tab with all the resources a Join column with the same value, you can create a data source in Tableau that joins the tabs to easily give you the combination of all dates and resources (it's effectively a cross join)'

                 

                Best Regards,

                Joshua

                • 5. Re: Unpivot calculation without adding new data source
                  Rytis Zolubas

                  Thanks for reply Joshua,

                   

                  I had to put my example in excel and change names for security reasons.

                  The main problem is that the client has provided us the data in wide format. We access our data only through dedicated Tableau server.

                  They ask us to see total of each Reasource by date (Monthly, quarterly, etc.). I have consulted my collegues but they could not find any solutions. That is why I am here.

                  We could ask the client to give us a new table but it is not very efficient since volumes are huge (hundreds of milions of rows). Ideally we could use the same table (like in the example) to generate resource usage reoprt.

                   

                  I still hope that the Tableau Community will be able to help or maybe more people would say that it is impossible and I could try to find solution outside tableau.

                   

                  Thanks again!

                   

                  Best regards,

                  Rytis

                  • 6. Re: Unpivot calculation without adding new data source
                    Joshua Milligan

                    Rytis,

                     

                    I understand.  My suggestion was not to change the source you have (knowing that you are unable to change it), but rather to supplement it with another source that you can control and blend the two together.  Is that a possibility?

                     

                    -Joshua