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: 
Anonymous
Not applicable

Chart Dimensions and Set Analysis

I keep running into this need. So far I've found work arounds that involve modifying the data model, but I need to see if there is a "right" way to do this.

We are a grocery chain. A simplified view of our data model includes dimensions such as products, baskets, store and Measures like Amount, cost, etc.

A fairly common need in my reporting is a chart - frequently a pivot table where product is one of the dimensions.

A very common measure used in our reports is Basket Average- Total Amount/# of Transactions.

If I need to make a pivot table where product is the dimension, and basket average is the measure, I only get the sum of the amount for the Product. I would guess this is because pivot tables partition the data by the product dimension. What I need is the total amount of all baskets where the basket contained this product.

I've tried several variations on this:

sum({$<%TransactionKey=P({$<Product=Product>}%TransactionKey)>}Amount)

I've also been experimenting with the AGGR function but have yet to produce the desired results.

I would imagine this is a fairly common need, someone must know how this is done. Please help. Thanks!

Sample Data (Simple Pivot Table):

Pivot.JPG.jpg

Net Sales: Sum({$<[PLU Code]=P(DG_PLUCode)>}Amount*NetFlag)

Transaction Count: Sum({$<[PLU Code]=P(DG_PLUCode)>}TransactionCount)

Set analysis is used to show only products that are in our "Sample Set"

Let's look at the first PLU listed: 85317600030 with 243 Transactions. My problems would be easily solved if I could display the total sales where that PLU is in the basket. The manual way to gather this information would be to select the PLU, then select possible transactions. Then de-select the PLU and calculate the sum Net Sales Amount.

I could then divide this number by the transaction count to recieve my desired basket average.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

This helped point me in the right direction, but ultimately figured it out myself.

sum({$<[PLU Code]=P(DG_PLUCode)>}aggr(sum({$<%TransactionKey=P({$<[PLU Code]=P(DG_PLUCode)>})>}Total<%TransactionKey> Amount*NetFlag),%TransactionKey))

Not too pretty but it's quick and gets me what I need.

View solution in original post

6 Replies
Nicole-Smith

Can you post a small example set of data?

Anonymous
Not applicable
Author

I edited my original post to further clarify.

Nicole-Smith

Without being able to play around with your actual .qvw, I can give you a route you may want to explore...

Since PLU is a dimension, we will want to use the total keyword to ignore it.

We can then use <> to denote which fields we do not want the total to ignore.

So I think you may need something like:

sum(total <Transaction> Amount)

This sum will ignore PLU (and any other fields), but it will include Transaction (you can also add other fields to the expression that you want it to include).

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

As Nicole suggested, this would be easier to answer if you could post a small sample QVW. But my guess is that you are not using the TOTAL qualifier in


sum({$<%TransactionKey=P({$<Product=Product>}%TransactionKey)>}Amount)


You are still trapped in the Product dim until you add TOTAL:


sum({$<%TransactionKey=P({$<Product=Product>}%TransactionKey)>} TOTAL Amount)


You may need to constrain TOTAL by another dim in the chart like Store.

sum({$<%TransactionKey=P({$<Product=Product>}%TransactionKey)>} TOTAL<Store> Amount)


-Rob


Anonymous
Not applicable
Author

This helped point me in the right direction, but ultimately figured it out myself.

sum({$<[PLU Code]=P(DG_PLUCode)>}aggr(sum({$<%TransactionKey=P({$<[PLU Code]=P(DG_PLUCode)>})>}Total<%TransactionKey> Amount*NetFlag),%TransactionKey))

Not too pretty but it's quick and gets me what I need.

Nicole-Smith

I'm glad you were able to figure it out