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
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER 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
Kush
MVP
MVP

try below then

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

View solution in original post

9 Replies
Almen
Creator
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?

fredrik_olsson
Contributor III
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). 

Almen
Creator
Creator

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

Kush
MVP
MVP

what is the expected outout?

fredrik_olsson
Contributor III
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. 

Thanks in advance

Kush
MVP
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? 

Almen
Creator
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.

 

 

Kush
MVP
MVP

try below then

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

View solution in original post

fredrik_olsson
Contributor III
Contributor III

Works great Kush, many thanks!