1 Reply Latest reply: Dec 3, 2009 12:56 AM by John Witherspoon RSS

    Set Analysis: How to "combine" multiple dimensions


      Here's my question simplified. I currently have a simple Item table supporting 2 charts. One chart displays Count of Items by Current Owner. The other displays Count of Items by Previous Owner. I want to combine the two charts into one, showing 3 columns: Name, # Items Currently Owns, # Items Previously Owned. And I need the selection on Status to be correctly reflected in this combined chart too.

      I imagine this is something that could be done in Set Analysis but couldn't get it to work. Can anyone take a look at this and help me out please?


      Here's the Item table.


      LOAD * INLINE [
      Id, Current Owner, Previous Owner, Status
      1, John, System, Open
      2, John, Mike, Open
      3, Mike, John, Closed
      4, Mike, John, Closed
      5, Mike, System, Open
      6, Mike, System, Open
      7, System, Mike, Closed
      8, System, Mike, Closed
      9, System, John, Open
      10,System, John, Open


        • Set Analysis: How to "combine" multiple dimensions
          John Witherspoon

          One way would be to load a separate "Owner" table, use "Owner" as your dimension, and use these as your expressions:

          count(if("Previous Owner"="Owner","Id"))
          count(if( "Current Owner"="Owner","Id"))

          If your data set is small, that might work fine. But if it is large, it will perform quite poorly, and you'll want another solution. One solution that would perform quite well would be to load an Item table with just the Id and Status, and load a separate Item Owner table with the Owner, TYPE of owner (current or previous), and the Id of the item they own or owned. I consider that a more robust solution in addition to the faster speed, though I don't know if it will apply exactly to your real situation. For instance, it wouldn't allow you to easily find an item that is currently owned by System but was previously owned by Mike. I've attached this solution as an example, showing how to build the Item Owner table from your original table using a crosstable load, and then one way of handling the chart (Owner and Owner Type as dimensions, count(Id) as the expression).

          You may also note that neither solution uses set analysis. Set analysis applies to an entire chart, not to a combination of dimensions within the chart, so the IF statements I wrote earlier can't be converted to set analysis. And when the data is converted along the lines I suggested, you no longer need any conditionals or set restriction at all, so it doesn't require and doesn't benefit from set analysis either.