Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Subtotals on TOP doesn't work properly

Hi,

  I have a problem with subtotals in my chart, the total is not equal to the values in lines, for example in the field "Valeur forecast" the total should be 11 113 and not 12 828, I don't want to apply the expression also in the totals, how can do that ?

Chart.png


my expression is : Sum ( [Forecast.Quantity) * AVG([Budget.Average Price]).

Thanks for your help.

1 Solution

Accepted Solutions

Re: Subtotals on TOP doesn't work properly

Try this:

Sum(Aggr(Sum ( [Forecast.Quantity) * AVG([Budget.Average Price]), Article, Designation))

View solution in original post

6 Replies

Re: Subtotals on TOP doesn't work properly

Try this:

Sum(Aggr(Sum ( [Forecast.Quantity) * AVG([Budget.Average Price]), Article, Designation))

View solution in original post

Not applicable

Re: Subtotals on TOP doesn't work properly

Hi Sunny,

  Thanks for your solution, it works but could you explain to me the expression please ?

regards,

Re: Subtotals on TOP doesn't work properly

Since this is a pivot table there is not an option available to use a total mode. What Aggregate is doing is creating a imaginary table as the one you have and summing its row to give the total up top. Does that make sense?

Not applicable

Re: Subtotals on TOP doesn't work properly

now I understand, Thanks.

Re: Subtotals on TOP doesn't work properly

No problem

I am glad I was able to help.

Best,

Sunny

MVP
MVP

Re: Subtotals on TOP doesn't work properly

Subtotal/Total in Pivot Table always works as Expression Total.. so you can't have the total of Rows which is usually available in Straight Table.

Consider below simple example..

Load * Inline

[

  Country, Customer

  Germany, A

  UK, B

  France, A

  UK, C

  USA, D

  India, E

  India, D

];

Create a Straight Table

Dimension

Country

Expression

1) Count(Distinct Customer)

Total Mode = Expression Total

You will get total 5

2) Count(Distinct Customer)

Total Mode = Sum of Rows

You will get total 7

Create a Pivot Table

Dimension

Country

Expression

1) Count(Distinct Customer)

You will get total 5


In Pivot Table, by default the total mode is Expression Total.

If you want, total of rows, you need to use one of the below 2 expressions

SUM(Aggr(COUNT(DISTINCT Customer),Country))

or

IF(Dimensionality() = 0,

     SUM(Aggr(COUNT(DISTINCT Customer),Country)),

     COUNT(DISTINCT Customer)

)

Hope this will helps !

Happy Qliking !