4 Replies Latest reply on Sep 20, 2012 4:56 PM by Kristian Miller

    Ordering and Top/Bottom Listing of Filtered Data

    Kristian Miller

      Hi all,

       

      First post, so if I'm missing some forum conventions fill me in. I've got a set of data in which I'm attempting to take a series of purchases and group them together (seems simple enough, right?). I've made two sheets in my example data file so you can see where my problem lies.

       

      In the "Initial" worksheet, the data is filtered out so that I'm looking at all purchases categories for Customer Type = 2, in Store = 2, that have the Successful flag (Successful = 1) and encompass all Types of Purchases across all Store Areas. This data is aggregated so I'm seeing the data as a percentage of all sales to Customer Type = 2. I've sorted this data to be Descending on the SUM of AmountSpentPerUpgrade and it works nicely for my purpose. I should also mention that the product categories are a "set" that I created between CategoryID and TypeOfPurchase. This is because there is overlap in some of the naming of products so I will have a series of distinct products, each with CategoryID = 23 but for different applications (TypeOfPurchase = 1, 2, 3, etc).

       

      So, now we arrive at my problem. I want to break up the data in one further step by adding in the "StoreArea" dimension as a Column. I've attempted this in the worksheet labelled "Attempt". When I do this I have two problems:

       

      - My StoreArea cross-sections no longer sorted properly. They continue to sort themselves in the same order as the Initial sheet. This means that each graph shows "23, 1" as the first bar, even though that par is often not the largest in the range (as would be expected in a Descending sort).

       

      - The Top 10/Bottom 10 function does not work on a Set. I have attempted to create the Index()/Last() calculations, as I've read about on the forums before, but since the data isn't ordered properly this is a failed endeavor.

       

      TL:DR - Want to view data by Store and StoreArea, showing the Sum of the AmountSpentPerUpgrade as a % of Total Spending. Would like to order data properly within StoreArea, and additionally would like to filter data down to the Top 10 and Bottom 10 purchased upgrades for a given StoreArea.

       

      Thanks for your help!