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

# total row

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.

Kamiel

• ###### total row

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

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

Regards,

Tjeerd Wieberdink

• ###### total row

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

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,

• ###### Re: total row

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.

• ###### total row

use below code

if ( dimensionality()=0,avg(aggr(count(Users),Date)), count(Users)))

hope this helps

• ###### Re: total row

Sunil Chauhan wrote:

use below code

if ( dimensionality()=0,avg(aggr(count(Users),Date)), count(Users)))

hope this helps