6 Replies Latest reply: Jan 7, 2014 1:05 PM by Nicole Smith RSS

    Chart Dimensions and Set Analysis

    Steve Taylor

      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.