total row

[kamielrajaram]

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

[tabletuner]

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

[kamielrajaram]

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

[vgutkovsky]

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

[johnw · Accepted solution]

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.

[SunilChauhan]

use below code

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

hope this helps

[johnw]

Sunil Chauhan wrote:

use below code

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

hope this helps

Exactly what Vlad said, then?