
Re: Pivot Table not calculating sub totals
Ruben Marin Sep 29, 2015 9:01 AM (in response to Yvonne Critchell)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])

Re: Pivot Table not calculating sub totals
Jonathan Poole Sep 29, 2015 9:02 AM (in response to Yvonne Critchell)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.

Re: Pivot Table not calculating sub totals
Yvonne Critchell Sep 29, 2015 11:21 AM (in response to Jonathan Poole )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
Ruben Marin Sep 29, 2015 11:33 AM (in response to Yvonne Critchell)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 ) ) 
Re: Pivot Table not calculating sub totals
Yvonne Critchell Sep 29, 2015 11:44 AM (in response to Ruben Marin )Yes Ruben you are correct, thank you!

Re: Pivot Table not calculating sub totals
Yvonne Critchell Oct 2, 2015 11:14 AM (in response to Ruben Marin )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
Ruben Marin Oct 5, 2015 2:48 AM (in response to Yvonne Critchell)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))

Re: Pivot Table not calculating sub totals
Jonathan Poole Oct 5, 2015 8:33 AM (in response to Yvonne Critchell)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.





Re: Pivot Table not calculating sub totals
Chris Deniziak Sep 29, 2015 11:04 AM (in response to Yvonne Critchell)You can also try to use the dimensionality() function to see where you are at in the calculation.