topic Re: Chart Dimensions and Set Analysis in QlikView

Chart Dimensions and Set Analysis

Anonymous — Tue, 07 Jan 2014 15:35:46 GMT

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

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.

Re: Chart Dimensions and Set Analysis

Nicole-Smith — Tue, 07 Jan 2014 16:06:10 GMT

Can you post a small example set of data?

Re: Chart Dimensions and Set Analysis

Anonymous — Tue, 07 Jan 2014 16:34:22 GMT

I edited my original post to further clarify.

Re: Chart Dimensions and Set Analysis

Nicole-Smith — Tue, 07 Jan 2014 16:56:10 GMT

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

Re: Chart Dimensions and Set Analysis

rwunderlich — Tue, 07 Jan 2014 17:17:19 GMT

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

Re: Chart Dimensions and Set Analysis

Anonymous — Tue, 07 Jan 2014 17:48:50 GMT

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.

Re: Chart Dimensions and Set Analysis

Nicole-Smith — Tue, 07 Jan 2014 18:05:12 GMT

I'm glad you were able to figure it out