Pivoting Measures from Column to Row

tablecalcs
customvisualization
visualizations
lookml

(Kyle) #1

Hi All,

Has anyone have any thought on how to pivot measures from the column to a row without have to make a derived table (see example below). I have thought about how possibly you could use parameters to do this but I am unsure on where to start. Any ideas or help here would be much appreciated!

Convert

To


(philip.martinelli) #2

Hey @kpmartin87,

There isn’t a way to do this that’s currently baked into the product, but there is a possible workaround.

Say I have these results, and I want to turn the measures into rows:

To do this, I could:

  1. Make a dummy case dimension with as many cases as there are measures.
    dimension: dummy_three { case: { when: { label: "Count" sql: 1=1 ;; } when: { label: "Count Inventory Items" sql: 1=1 ;; } when: { label: "Count Orders" sql: 1=1 ;; } }

  2. Pivot on your original dimension, include the dummy dimension, and dimension fill the dummy.

  3. Make a table calc that fills in the table.
    coalesce( if(row()=1, ${order_items.count}, null), if(row()=2, offset(${order_items.count_orders}, 1-row()), null), if(row()=3, offset(${order_items.count_inventory_items}, 1-row()), null), null)

  4. Hide the measures.

Feel free to hop on chat or email support@looker.com if you have more questions!

Thanks,
Philip


(Kyle) #3

Thanks @philip.martinelli!!! This should work great!


(Brecht Vermeire) #4