Solved: Subtotal showing Average - Qlik Community

Report Inappropriate Content · ‎2014-04-23

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

Report Inappropriate Content · ‎2014-04-23

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.

View solution in original post

MK_QSL · ‎2014-04-23

In Pivot Table, Subtotal is taking the Expression Total...

To overcome the problem, you need to use Dimensionality() Function..

Kindly upload sample file, we can help you only..

cfz · ‎2014-04-23

Hi Baikan,

On a pivot table the total is calculated as an expression total so you need to incorporate the sum to the expression. Try adding a sum at the beginning of your expression.

I hope it helps.

Carlos

Report Inappropriate Content · ‎2014-04-23

Thank you very much for your advice.

Can you explain Dimensionality() to me?

Thank you.

Report Inappropriate Content · ‎2014-04-23

Thank you Carlos.

But it said there is an error after I add the sum in front of AVG()

Paco

MK_QSL · ‎2014-04-23

Just type Dimensionality() as an expression... You will know it by the result. If you provide sample data, we can help you better way.

CELAMBARASAN · ‎2014-04-23

May be something like

If(IsNull(RowNo()) OR RowNo()=0, Sum(Aggr( Avg( Aggr(...) ), Department)), Avg(Aggr(...)))

Report Inappropriate Content · ‎2014-04-23

I am new to Qlikview.

This app has been developed for a while and it gets complicated. (I know I should keep it as simple as possible)

I am sorry that I don't know how to make up some sample data to replicate the situation.

Thank you very much for your advice.

I type in Dimensionality() and all fields display 1 except for the subtotal row.

What is that mean?

Paco

cfz · ‎2014-04-23

Hi Baikang

You have to put sum(aggr(your expression), dimensions affected). If you upload the qvw I could try to look at it.

I hope it helps.

Carlos

Report Inappropriate Content · ‎2014-04-23

Can you try by changing the total mode in the expressions tab to 'Sum' of rows and check if the result is as expected?