Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP

try below then

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

View solution in original post

9 Replies
Highlighted
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?

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

Highlighted
Creator
Creator

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

Highlighted
MVP
MVP

what is the expected outout?

Highlighted
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

Highlighted
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? 

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

 

 

Highlighted
MVP
MVP

try below then

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

View solution in original post

Highlighted
Contributor III
Contributor III

Works great Kush, many thanks!