Pivot Table not calculating sub totals

[yvonne-c]

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

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

[rubenmarin]

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])

[JonnyPoole · Accepted solution]

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.

[Anonymous]

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

[yvonne-c]

Thanks Jonathan

Each product has a CCC value

This expression works fine

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

[rubenmarin]

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]

Yes Ruben you are correct, thank you!

[yvonne-c]

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

[rubenmarin]

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

[JonnyPoole]

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.