Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fredrik_olsson
Contributor III
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:

OrderCodeOrderDateOrderCtr
1232020-07-291
1242020-07-291
1252020-07-291

 

Product:

OrderCodeProductCategoryProductNameProductPrice
123ToysBall45
123BooksPippi12
123ToysDoll 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
Kushal_Chawda

try below then

=sum(ProductPrice)/sum(total <OrderDate>aggr(sum(OrderCtr),OrderDate))

View solution in original post

9 Replies
Almen
Creator II
Creator II

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?

fredrik_olsson
Contributor III
Contributor III
Author

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

Almen
Creator II
Creator II

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

Kushal_Chawda

what is the expected outout?

fredrik_olsson
Contributor III
Contributor III
Author

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. 

Thanks in advance

Kushal_Chawda

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? 

Almen
Creator II
Creator II

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.

 

 

Kushal_Chawda

try below then

=sum(ProductPrice)/sum(total <OrderDate>aggr(sum(OrderCtr),OrderDate))
fredrik_olsson
Contributor III
Contributor III
Author

Works great Kush, many thanks!