topic Re: Subtotal showing Average in QlikView

Subtotal showing Average

Wed, 23 Apr 2014 06:31:45 GMT

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

Re: Subtotal showing Average

MK_QSL — Wed, 23 Apr 2014 06:38:10 GMT

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..

Re: Subtotal showing Average

cfz — Wed, 23 Apr 2014 06:39:00 GMT

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

Re: Subtotal showing Average

Wed, 23 Apr 2014 06:48:42 GMT

Thank you very much for your advice.

Can you explain Dimensionality() to me?

Thank you.

Re: Subtotal showing Average

Wed, 23 Apr 2014 06:50:49 GMT

Thank you Carlos.

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

Paco

Re: Subtotal showing Average

MK_QSL — Wed, 23 Apr 2014 06:52:29 GMT

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

Re: Subtotal showing Average

CELAMBARASAN — Wed, 23 Apr 2014 06:57:10 GMT

May be something like

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

Re: Subtotal showing Average

Wed, 23 Apr 2014 07:02:23 GMT

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

Re: Subtotal showing Average

cfz — Wed, 23 Apr 2014 07:24:39 GMT

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

Re: Subtotal showing Average

Wed, 23 Apr 2014 07:29:17 GMT

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

Re: Re: Subtotal showing Average

qlikpahadi07 — Wed, 23 Apr 2014 08:08:24 GMT

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

Re: Re: Subtotal showing Average

qlikpahadi07 — Wed, 23 Apr 2014 08:11:58 GMT

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.

Re: Subtotal showing Average

Wed, 23 Apr 2014 23:40:38 GMT

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.