Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yvonne-c
New Contributor

Pivot Table not calculating sub totals

Hi

I am trying to create a pivot table with a calculation as a column.  The calculations are all working correctly but the sub-total does not always appear.

The sum(quantity) comes from one table, and the calculated course from another.

I want to create a bar chart showing the no calculated courses per class, which I need the sub totals for

pivot1.PNG

I think I read somewhere that I should carry out the calculation in the script, but I don't know where to start!  I'm struggling to find any help videos or documents

1 Solution

Accepted Solutions
Employee
Employee

Re: Pivot Table not calculating sub totals

How many values for [Calculated Cow Course] are there for each [Class] and how many for each [Product] ? 

Right now Qlik knows how to sum up all the quantity values for both Class and Product, because of the sum () function.

But if it encounters more than one Calculated Cow Course value, its not going to know how to aggregate... Avg ? Sum ? Min ? Max ?  there is nothing in the expression to define that even if all the values are the same. 

ie:   Sum(Quantity) / Min([Calculated Cow Course])

or you may need to subtotal each and then aggregate

sum (   aggr(   sum(Quantity)/[Calculated Cow Course] , Product )    )

...this would calculate  sum(Quantity) / [Calculated Cow Course) at the product level and then sum the results for the [Class] level.

Depends what the calculation you want to achieve is.

9 Replies

Re: Pivot Table not calculating sub totals

Hi Ivonne, seems that there is a different value for [Calculated Cow Course] for each Product, so the subtotal founds more than one value in that field and can't do the operation, try with:

Sum(Aggr(Sum(Quantity)/[Calculated Cow Course], Class, Product))

or maybe:

Sum(Quantity/[Calculated Cow Course])

Employee
Employee

Re: Pivot Table not calculating sub totals

How many values for [Calculated Cow Course] are there for each [Class] and how many for each [Product] ? 

Right now Qlik knows how to sum up all the quantity values for both Class and Product, because of the sum () function.

But if it encounters more than one Calculated Cow Course value, its not going to know how to aggregate... Avg ? Sum ? Min ? Max ?  there is nothing in the expression to define that even if all the values are the same. 

ie:   Sum(Quantity) / Min([Calculated Cow Course])

or you may need to subtotal each and then aggregate

sum (   aggr(   sum(Quantity)/[Calculated Cow Course] , Product )    )

...this would calculate  sum(Quantity) / [Calculated Cow Course) at the product level and then sum the results for the [Class] level.

Depends what the calculation you want to achieve is.

chris_deniziak
Contributor

Re: Pivot Table not calculating sub totals

You can also try to use the dimensionality() function to see where you are at in the calculation.

yvonne-c
New Contributor

Re: Pivot Table not calculating sub totals

Thanks Jonathan

Each product has a CCC value

This expression works fine

ie:   Sum(Quantity) / Min([Calculated Cow Course])

Re: Pivot Table not calculating sub totals

Hi Yvonne, please check if the results are correct, that way you are using the lower [Calculated Cow Course] from all the products in the class, and I think each product should have his [Calculated Cow Course].

Check if the result with the other expression proposed by Jonathan gives you the expected result:

sum (   aggr(   sum(Quantity)/[Calculated Cow Course] , Product ))
yvonne-c
New Contributor

Re: Pivot Table not calculating sub totals

Yes Ruben you are correct, thank you!

yvonne-c
New Contributor

Re: Pivot Table not calculating sub totals

Hi all

Would you be able to help me with a further sum to my calculation?  I want to add another column to my pivot table which divides the sum of the last expression by  'NumCow' which is stored in a third table.

I have tried adding 'numcows' to the current expression as follows

Sum(Aggr(Sum(Quantity)/[Calculated Cow Course]/NumCows, Product))

This seems to calculate fine most of the time, until I select more than one customer, then I get nil values.  I assume I need to put 'per customer' into the expression somewhere as you did for product?   But I' not sure where to put it?

Thanks

Re: Pivot Table not calculating sub totals

Hi Yvonne, you can add a dimension to Aggr() just adding a parameter with the dimension name:

Sum(Aggr(Sum(Quantity)/[Calculated Cow Course]/NumCows, Product, Customer))

Employee
Employee

Re: Pivot Table not calculating sub totals

I agree with Ruben with regards to the customer dimension, but you may still have in instance where you have multiple records for any given product/customer combination. In that situation, you really need to aggregate [Calculated Cow Course] and [NumCows] with an appropriate aggregation function.  

What is the right one ?   avg() , sum(), only() , min()...   that depends on the data.  

If you don't want multiple records, then you may need to put [Calculated Cow Course] and [NumCows] as a field in the customer or product table to ensure its treated not as a measure but as an attribute of the dimension. Its the same principal for a product that has a product price.  The price  has 1 value per product.  But, you may change the price in different orders in which case price changes with the product, the order, the customer... anything in the sales transaction. In that case, price should live in the orders table. 

In your case if your 'price' measure ( Calculated Cow course or numcows) has only 1 value per customer or product then you may be able to avoid an aggregation function by changing the data model to have those 2 measures in the customer or product table. 

probably more than you wanted to get into but if you can't get the expression to work AND you can't think of the right aggregation function, then its probably because the data model isn't reflective of what you are trying to do and needs a small change.

Community Browser