1 Reply Latest reply on Jun 25, 2018 2:01 PM by Jim Dehner

    Why do my row grand totals only sum rows with one entry?

    Jessica Singer

      Some of you have seen similar files from me. I seem to be circling the same set of issues that I don't understand.

       

      Attached you will find a workbook with a sheet called "total hours". This is just a summation of total instruction hours offered per season and program type and the number of staff assigned to each program site. Ultimately, I want to be able to say how many hours of instruction were offered at each site, by season, year, and in total. I'd also like to be able to filter these by city council district but that may require a data blend and another post entirely.

       

      When I add row grand totals and column grand totals to the crosstab in "total hours", if the rows contain more than one entry the grand totals are blank. Why? If this is not how to get to the aggregate numbers I described above, how should I do it?

       

      Thank you.

       

      Jessica

        • 1. Re: Why do my row grand totals only sum rows with one entry?
          Jim Dehner

          Hi Jessica

          please see the attached

           

          2 things were goin on

          first

           

          the attr in the total hours calc is not going to aggregate - attr([Hours per Season])*([Number of Staff])

          That is you cant add attribute together  I change it to this

                                   avg([Hours per Season])*([Number of Staff])

           

          then change the aggregation to Sum

           

           

          that will get you an solution but not an answer - the big problem is that your data is poorly structured - you have this combined field with abbreviated days of the week that you then separated out into separate dimensions

          suggest you look at TAbleau Prep to clean and reshape the data into a tall thin data set -

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.