7 Replies Latest reply on Aug 2, 2016 10:00 AM by Cheryl Morgan

    Totals

    Cheryl Morgan

      Good morning,

      I cannot get the total that I need. I have attached an excel worksheet because our network will not let send the right data. I have place what I need in red. I have 'Buckets' for my data, and I need to calculate the total for all buckets, in this case it will be in a specific time frame. Then I need the percent, but that should be easy if I could just get  total.

       

      I just hop it will let me attach the excel sheet.

      Cheryl

        • 1. Re: Totals
          Andrew Watson

          Try this:

           

          {FIXED [Time Frames]:SUM([January])}

           

          Or if your data is actually better structured behind the scenes (i.e. flatter) then you could alter the formula to something along the lines of:

           

          {FIXED [Time Frames],MONTH([Date]),YEAR([Date]):SUM([Measure])}

           

          Obviously that formula would need to change to reflect your actual field names.

          1 of 1 people found this helpful
          • 2. Re: Totals
            Carl Slifer

            Hi Cheryl,

             

            Your data structure is not great for tableau. You will need to fix this either with a pivot within tableau (not the best) or from outside with an ETL tool.

             

            You will want the following columns from a raw data structure in order to make best use of tableau

            TimeFrames     Buckets   Date      Value    

             

            If the data is already in this form and you copied data from tableau and didn't explain that above then depending how your tableau is currently set up and I will imagine you have the following:

            Columns: YEAR([Date]), MONTH([Date])

            Rows: TimeFrames, Buckers

            Text: SUM([Value])

             

            Now what you need to do is Right Click the SUM([Value]) and quick table calculation, choose percent of total. 

            Then right click it once again and go to compute using and choose 'pane down'.  If for some reason you still need the TOTAL next to each one of these create a new calculated field. WINDOW_SUM(SUM([Value])) and drag that field from your measures pane onto the text table. You will notice that by doing this Measure Names was added to your columns shelf or rows shelf and measure values is now on your text shelf and there will be a measure values card immediately underneath your marks card. Right click the new measure on the measure values card (this window sum one) and change it to compute using pane down as well.

             

            If this doesn't help a mockup of your data and the issue you're having inside tableau would be the most appropriate course of action for your to take next.

             

            Best Regards,

            Carl Slifer

            InterWorks

            1 of 1 people found this helpful
            • 3. Re: Totals
              Carl Slifer

              Hi Andrew,

               

              An LOD is a bit out of place here as the functionality can be done with a table calculation. Using a table calculation instead would minimise the querying done to the the database and it would further complicate the issues of having to make calculations behave according to filters, etc.

               

              LODs are a fantastic tool but they get overused quite a bit when a simpler solution exists.

               

              Cheers,

              Carl Slifer

              InterWorks

              • 4. Re: Totals
                Andrew Watson

                Carl, agreed in some situations, perhaps not in others - difficult to say what is most approriate here as we don't know what the underlying data looks like. Based on the supplied Excel it appears in this case the table calculation would need to restart every month/year/time frame which makes it more complex.

                 

                As you know there are multiple ways to skin a cat in tableau, we don't have the necessary information in this case to say what is the best way to skin this one :-)

                • 5. Re: Totals
                  Cheryl Morgan

                  I apologize, I should have stated that my data is coming in as string values from SQL Server. Every time I try to convert I am getting an error. Sorry I am working on 2 things at once, and forgot the important part.

                  • 6. Re: Totals
                    Cheryl Morgan

                    You are absolutely correct. Each month will have its own total. It is not cumulative. Each month is loaded into SQL. Also, the data is loaded as a string. I need to have the percentage equal to 100% for the month. for instance it will be 2%, 5%, 67%, 11%, 15% respectively. This gets for complicated way I have to add the hospital unit into to the mix, but I will deal with that later.

                     

                    Thank all of you for your help, as you can tell I am very green at this. I read a couple of  articles (Customizing Grand Totals – Part 1 | Drawing with Numbers ) that made this seem as if I will not be able to do this.

                    • 7. Re: Totals
                      Cheryl Morgan

                      The good news is I have the percentages working. I just cannot get the total to show.