5 Replies Latest reply on Sep 14, 2016 5:31 AM by Lee Forst

    Help with a table calc; addressing a dim not in view

    Lee Forst

      We have a data source that has for each Timecard Entry Date, the possible working hours for each month.  In the attached example workbook, you will see the Year of Timecard Entry Date and Month of Timecard Entry Date is on the rows shelf.  The AVG of Possible Working Hrs For Month is placed on the shelf, and that will give us the correct hours for the year/month.  Also on the shelf is a table calc that is an AVG of the running total summing along Year and Month of Timecard Entry Date restarting every Year of Timecard Entry Date.  This gives the total hours by each year for the last month of the year.  For example, 2015, will show 2088 for December.  If I pull off Month of Timecard Entry Date, the table calc stops working because it can no longer address Month of Timecard Entry Date.  How do I solve this so that I end up with in view Year of Timecard Entry and the total for the year.  See below for the example of what I need.

       

        

      Year of Timecard Entry DateAvg. Possible Working Hrs For Month
      2014344
      20152088
      20161568
        • 1. Re: Help with a table calc; addressing a dim not in view
          Matt Hong

          Lee,

           

          I've attached my solution. However, it seems as though you are running an older version of Tableau. Let me know if there is an issue.

          • 2. Re: Help with a table calc; addressing a dim not in view
            Lee Forst

            Thanks Matt for responding. Yes, we are still running 9.3, so I can't open up what you sent.  Care to explain the solution or upload that version of the solution?

            • 3. Re: Help with a table calc; addressing a dim not in view
              Matt Hong

              Step 1.

                   Create a parameter that will toggle between strings 'Month' and 'Year'. I called it By month or year?

               

              Step 2.

                   Create a calculated field that is a string combining each month with the corresponding year. I called this Month and year combined:

              STR(YEAR(DATE([Timecard Entry Date])))

              + '/'

              + STR(MONTH(DATE([Timecard Entry Date])))

                   Unless you do this, toggling between month and year will aggregate all values under, let's say, April 2015 and 2016, when 'Month' is chosen.

               

              Step 3.

                   Create a calculated field that will be either the months or the years, depending on the parameter from Step 1. I called it Month or year toggle:

              if [By month or year?] == 'Month'

              then [Month and year combined]

              else STR(YEAR(DATE([Timecard Entry Date]))) end

               

              Step 4.

                   Create a calculation that will take the average of possible working hours, except at a fixed level of detail. I called it Avg possible working hours:

              { FIXED [Month and year combined]: AVG([Possible Working Hrs For Month])}

               

              Step 5.

                   Create a calculation that takes the running sum of the above LOD calculation. I called it Running total working hrs:

              RUNNING_SUM(ATTR([Avg possible working hours]))

               

              Step 6.

                   Place the pills on rows in this order: YEAR(Timecard Entry Date), Month or year toggle, SUM(Avg possible working hours), Running total working hrs.

               

              Step 7.

                   For the Running total working hrs table calculation, compute using Month or year toggle.

               

              Step 8.

                   Display the parameter control for [By month or year?] and play around.

              • 4. Re: Help with a table calc; addressing a dim not in view
                Matt Hong

                This workbook might work in your version, but you need to locate the extract (it is just a .twb). I am able to modify it so that it opens in old versions, but I can't save it as a .twbx because I don't actually have the old version.

                • 5. Re: Help with a table calc; addressing a dim not in view
                  Lee Forst

                  Matt, I downloaded this version and looked at it and your written explanation above.  It makes sense and will get me where I need to go.  Good work, thank you!