topic total row in QlikView

total row

kamielrajaram — Mon, 14 Nov 2011 15:11:49 GMT

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

tabletuner — Mon, 14 Nov 2011 15:17:05 GMT

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

kamielrajaram — Mon, 14 Nov 2011 15:42:25 GMT

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

vgutkovsky — Mon, 14 Nov 2011 18:32:29 GMT

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

johnw — Tue, 15 Nov 2011 01:16:51 GMT

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

SunilChauhan — Tue, 15 Nov 2011 05:46:31 GMT

use below code

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

hope this helps

Re: total row

johnw — Tue, 15 Nov 2011 16:58:29 GMT

Sunil Chauhan wrote:
use below code
if ( dimensionality()=0,avg(aggr(count(Users),Date)), count(Users)))
hope this helps

Exactly what Vlad said, then?