1 Reply Latest reply on Dec 16, 2016 9:48 PM by Shinichiro Murakami

    Stacking dimensions

    Francois Houde
    Francois Houde Dec 16, 2016 12:07 PM

      Hi all, I'm having 3 dimensions in my dataset : Store Type (Physical / virtual), Store Size (small, medium, large), Products (electronics, furniture, food)

       

      If I put these 3 dimensions in the "Rows" I'll have something like this :

      Physical / small / electronics -- #number of records

      Physical / small / furniture -- #number of records

      Physical / small / food -- #number of records

      Physical / medium / electronics -- #number of records

      Physical / medium / furniture -- #number of records

      .....

       

      So I'll get 18 rows

       

      What I really want is :

       

      Physical -- #number of records

      Online -- #number of records

      Small -- #number of records

      Medium -- #number of records

      Large -- #number of records

      Electronics -- #number of records

      Furniture -- #number of records

      Food -- #number of records

       

      Any idea on how I could do this ?

       

      Thanks

      • 151 Views
      • Categories: Formatting & Display
      • Tags:
        • 1. Re: Stacking dimensions
          Shinichiro Murakami
          Shinichiro Murakami Ambassador Dec 16, 2016 9:48 PM (in response to Francois Houde)

          Hi Francois

           

          Two different approaches.

          Which is better ?  depends on your preference, but considering the risk of triple counts, Dashboard approach should be easier and simpler .

           

          1. Dashboard

          Create three different worksheets (Blue in the example), and merge them on the dashboard.

          Table is just straight forward.

           

           

           

          2. Union (only available on 9.3 or newer version)  = Red worksheet

          This triples data set and pick up data only for particular category from respective data source.

          This means there is a risk of triple countiing in some cases.

           

          Create Union and create two calculated fields based on table name.

           

           

           

          [Category]

          case [Table Name]

          when "Clipboard_20161216T181903.txt" then [Prod.]

          when "Clipboard_20161216T181903.txt1" then [Type]

          when "Clipboard_20161216T181903.txt2" then [Size]

          end

           

          [Top Category]

          case [Table Name]

          when "Clipboard_20161216T181903.txt" then "Prod"

          when "Clipboard_20161216T181903.txt1" then "Type"

          when "Clipboard_20161216T181903.txt2" then "Size"

          end

           

           

           

          Thanks,

          Shin