6 Replies Latest reply: Nov 15, 2011 11:58 AM by John Witherspoon RSS

    total row

    Kamiel Rajaram

      Good day,

       

      Please could anyone assist with the following,

      I have a pivot table with a date dimension and I count the number of users per day. What I need is to then have a total row that shows the average for the date range.

      Thank you for your assistance

       

      Kamiel

        • total row
          Tjeerd Wieberdink

          The total row in a pivot table listens to rowno()=0.

          So your expression could be:

           

          if(rowno()=0,avg(Users), count(Users))

           

          Regards,

           

          Tjeerd Wieberdink

            • total row
              Kamiel Rajaram

              Thank you for the quick response, the issue I have is that I only have users for specific days of the month and any user can be present on any day. is it possible to total the users, even over the date range and not distinct users

                • total row
                  Vlad Gutkovsky

                  Slight modification of Tjeerd's logic: if(rowno()=0,avg(aggr(count(Users),Date)), count(Users))

                   

                  This will give you the average users per day. I personally prefer dimensionality()=0 to rowno()=0 by the way; seems to work better for some reason.

                   

                  Regards,

                  Vlad

                    • Re: total row
                      John Witherspoon

                      I'd expect you to be able to just use this much of that expression:

                       

                      avg(aggr(count(Users),Date))

                       

                      If you're on a Date row, the average should just be of a single value, and should come out right, I think.  So I don't think you'd need to check rowno() or dimensionality() in this case.  I'm not sure how or if performance would differ between the expressions, or whether that matters.

                       

                      Edit: Checked it.  It works.