4 Replies Latest reply on Jun 21, 2016 5:03 PM by zibal jafri

    Row Total ignoring few values

    zibal jafri

      Hello Everyone,

       

      I have a dimension "Category" with values as shown below. I want to calculate the row total without the value of "Do Nothing" attribute.

       

       

      I have attached the .twbx file for reference.

       

      Thanks,

      Zibal

        • 1. Re: Row Total ignoring few values
          Michel Caissie

          Zibal,

           

          There is a little trick to do this.

           

          First thing to  understand is that Tableau when computing the Total removes the level of granularity.

          If you check on sheet 3 , I have a simple computation of the attribute function ATTR(Category) and for every row Tableau returns the Category itself ( obviously for each category there is a single attribute) but for the Total, Tableau display a *  because there is not a single value of Category in the View.

           

          We can take advantage of this behavior by replacing the TimeSpent  with the following calculation

          //return value for each cell

          if ATTR( [Category] ) = ATTR( [Category] ) then SUM( [Time Spent] )

          //return value for Total

          else SUM( (if [Category] != 'DO Nothing' then [Time Spent] end) )

          end

           

          So for each row ATTR( [Category] ) = ATTR( [Category] ) will return true and display the Time Spent in each cell but when computing the Total it will return false, in which case you will display the adjusted sum of Time Spent without considering the  category  `DO Nothing

           

          Michel

          • 2. Re: Row Total ignoring few values
            zibal jafri

            Hi Michel,

             

            Thanks for your response. That's really a great way to deal with such problems. I am facing another problem now. I have created a calculated field 'Percent' which calculates the percent of time spent on each category. When I am trying to get the Row Total for the percent field excluding "DO Nothing" value, I am getting no result as shown below:

             

             

            I can't understand why. Could you please help me with this issue ? Attached is the file for reference.

             

            Regards,

            Zibal

            • 3. Re: Row Total ignoring few values
              Michel Caissie

              All you need to do is to apply to same logic I did.

              You already have the Percent for each cell,  what is missing is the computation for your Grand Total.

              I dont know what kind of percentage exactly you are looking for the Grand Total but

              you can get the Time Spent sum  using the previous example.

              SUM( (if [Category] != 'DO Nothing' then [Time Spent] end) )

              For the denominator I dont know exactly what you need but you can get the AVG of Net Hours using a similar function.

              AVG( (if [Category] != 'DO Nothing' then [Net Hours] end) )

               

              In other words , build a calculation that would give you the expected result on a worksheet with only Name on Rows, and use this calculation in the //return value for Total  of the previous example.

              • 4. Re: Row Total ignoring few values
                zibal jafri

                Hi Michel,

                 

                Thanks for your response. The problem is that the calculated field (Percent) is already an aggregated field. So when I want to sum the Percent while returning the total by your method, I get an error saying that  the argument passed to sum function is already aggregated and can't be aggregated further. The calculated field (Percent) is calculated as below:

                 

                Sum ([Time Spent ])/ Get Denominator

                 

                Where Get Denominator is again a calculated field calculated as:

                 

                Get Denominator:

                 

                If ATTR([Category]) = "Eating"

                then

                ((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -1)

                ELSEIF

                ATTR([Category]) = "Leisure"

                then

                ((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -2)

                ELSEIF

                ATTR([Category]) = "Sleeping"

                then

                ((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -3)

                ELSEIF

                ATTR([Category]) = "Till Date"

                then

                ((AVG([Net Hours]))) - LOOKUP(SUM([Time Spent]), -4)

                ELSEif

                ATTR([Category]) = "DO Nothing"

                then

                AVG([Net Hours])

                END

                 

                I have tried so many things to resolve this issue but no luck so far. Please help me if possible. Attached is the twbx for your reference.

                 

                Regards,

                Zibal