Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases.
**IMPORTANT DETAILS**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Pivot Table not calculating sub totals

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

yvonne-c

Creator

2015-09-29
08:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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

3,229 Views

1 Solution

Accepted Solutions

JonnyPoole

Employee

2015-09-29
09:02 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

rubenmarin

MVP

2015-09-29
09:01 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,194 Views

JonnyPoole

Employee

2015-09-29
09:02 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Not applicable

2015-09-29
11:04 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,194 Views

yvonne-c

Creator

2015-09-29
11:21 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Jonathan

Each product has a CCC value

This expression works fine

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

2,194 Views

rubenmarin

MVP

2015-09-29
11:33 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,194 Views

yvonne-c

Creator

2015-09-29
11:44 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Yes Ruben you are correct, thank you!

2,194 Views

yvonne-c

Creator

2015-10-02
11:14 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,194 Views

rubenmarin

MVP

2015-10-05
02:48 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,194 Views

JonnyPoole

Employee

2015-10-05
08:33 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

2,194 Views

Community Browser