Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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

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.

View solution in original post

9 Replies

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

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.

View solution in original post

Creator
Creator

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

Creator
Creator

Thanks Jonathan

Each product has a CCC value

This expression works fine

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

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

Yes Ruben you are correct, thank you!

Creator
Creator

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

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

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.