Subtotal showing Average

[#1]

Hi everyone:

My problem happened in a pivot table which has a date dimension on the top and team dimension on the left.

When I tick the subtotal check box and have 'AVG(AGGR(......),Team,Date)' in my expression, the subtotal on the top actually showing the average value for department.

Is it possible to show the total of average value in individual department in the subtotal field and average in each department row?

For example:

What I have now is followed.

Total	122
AC/Ops	461
BD	57
CRM	64
Finance/Legal	38
HR	32
IT	86
Leasing	266
Marketing	69
MGMT	50
PBI/MSG	29
Sales	78
Service Centre	212
Fleet	147

Obviously, the 122 is not the total of the values under. Is it possible to change the 122 to be the total?

Many thanks.

Paco

[qlikpahadi07]

Dear Baikang,

I have attached a Sample may be clear your Doubt, as you are not able to attach your QVW it's hard to make you understand.

anyways you can try something like

if(Dimensionality()=0,

  sum(aggr(your expression), dimensions, <your current Expression >)

may be helpful to you

[qlikpahadi07]

Also if still you are not able to achieve try this:

change your pivot table to Straight

select the option highlighted below and check your result it must be as you wanted.

[#4 · Accepted solution]

Thank you very much QlikPahadi07!

And Thanks to everyone's help, I have found the solution.

I am using the Dimensionality().

Remember my original expression is something like this: AVG(AGGR(SUM(......),Team,Date)).

Then I add a condition to check when dimensionality()=0, I took the Team out of AGGR. And the expression would average across Date for the total in Team.

Thank you very one.