# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for
Did you mean:
Highlighted
Contributor III

## Sum total in pivot table

Hi,

I'm trying to add a calculation in a pivot table, dividing the sales amount for a certain product with the total number of orders for that certain date.

Order:

 OrderCode OrderDate OrderCtr 123 2020-07-29 1 124 2020-07-29 1 125 2020-07-29 1

Product:

 OrderCode ProductCategory ProductName ProductPrice 123 Toys Ball 45 123 Books Pippi 12 123 Toys Doll 32

I have the OrderDate as rows and the the ProductName as columns, but only withProductCategory = Toys like this:if (ProductCategory = 'Toys', ProductName)

In the measures I'm trying to do this calculation, but I'm not getting the total number of orders back:
SUM(ProductPrice) / SUM(TOTAL <OrderDate> OrderCtr)

Any help is appreciated!

1 Solution

Accepted Solutions
Highlighted
MVP

try below then

``=sum(ProductPrice)/sum(total <OrderDate>aggr(sum(OrderCtr),OrderDate))``
9 Replies
Highlighted
Creator

If you need the total number of order per day disregarding the ordercode it should be

SUM(ProductPrice) / SUM(TOTAL {<OrderCode=>} OrderCtr)

or do I misunderstand your query?

Highlighted
Contributor III

Thanks Almen for trying to help out.

That didn't work. I need to be able to divide the SUM(Sales) with the total amount of orders that date, independently if the order contains any products in the selected category (toys).

Highlighted
Creator

So for every category per day you want Sum(Sales)/Quantity?

Highlighted
MVP

what is the expected outout?

Highlighted
Contributor III

I want to divide the total sales for every product, with the total number of orders for that day, independently if that order contains the product or not, i.e. for 2020-07-29 I want to divide with 3 as there are 3 orders that day.

Highlighted
MVP

this below expression should work.

SUM(ProductPrice) / SUM(TOTAL <OrderDate> OrderCtr)

Would you be able to share what you are trying to do? Which chart you are creating? What are the dimensions ? What is the expected numbers?

Highlighted
Creator

It doesn't work in pivot because of the if statement [if (ProductCategory = 'Toys', ProductName)]in your dimension. It reduces the total number of orders because books is not part of the dimension. Put ProductCategory as Dimension 1 and ProductName as Dimension2 and the expression

SUM(ProductPrice) / SUM(TOTAL <OrderDate> OrderCtr) will work.

Highlighted
MVP

try below then

``=sum(ProductPrice)/sum(total <OrderDate>aggr(sum(OrderCtr),OrderDate))``
Highlighted
Contributor III

Works great Kush, many thanks!