Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

Try this:

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

View solution in original post

6 Replies
sunny_talwar

Try this:

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

Not applicable
Author

Hi Sunny,

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

regards,

sunny_talwar

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
Author

now I understand, Thanks.

sunny_talwar

No problem

I am glad I was able to help.

Best,

Sunny

MK_QSL
MVP
MVP

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 !