Keep dimension of pivot table visible in export when there are no results

Looker Q&A Looker
,
1

Hi all,

I’ve created a data table which included a pivot table. When I export the data to a xlsx. it only shows the dimension with actual results. This means the dimensions move to left if a certain dimension doesn’t have any results. This is very inconvenient because the xlsx. is loaded to software of another company. Is there a simple way the keep all dimensions visible in the export? Or as an alternative: move the order of the dimensions within the pivot table. This is not possible in the data view, only the visualization. I do prefer the first method.

Hope anyone can help me. I am a simple data analyst with little development skills.

Thanks in advance
Bert

2

I don’t think this will be possible with a pivot table. As you’ve mentioned, only the columns with data will be shown in a pivot table.

You could work around this by not using a pivot table. You’d need to create a filtered measure for each pivot column. Looker won’t exclude measures if they have no data, so this way they would all show up in your export. Of course, this means you have to continue creating new measures if new pivot values arise in the future.

3

Hi Sam,

Thanks for your response. Could you elborate how this can work with filtered measures. Maybe you have an example.

I also tried to use table calculations like this:

coalesce(pivot_index(${dummy.value},3),0)

Now it only shows the results of the table calculations in the export, but it doesn’t solve the problem. When there are no results for a certain dimension in the pivot table the results of the table calculations all move.

4

Sure, I’ll write up a little example.

Say you have a list of orders per day, pivoted by category.

Deluxe Retro Modern
32 64 243
24 25 456
24 98 856
21 56 243
14 67 865
11 87 234

To unpivot the table but show the same data, you’d need to create a filtered measure on order count for each possible category value.

measure: count_orders_deluxe {
  type: count
  sql: ${orders.id} ;;
  filters: { filters: [category: "deluxe"] }
}

measure: count_orders_retro {
  type: count
  sql: ${orders.id} ;;
  filters: { filters: [category: "retro"] }
}

measure: count_orders_modern {
  type: count
  sql: ${orders.id} ;;
  filters: { filters: [category: "modern"] }
}
5

Hi Sam,

Great, many thanks. In the end I did not have to use a custom lookml code for the filtered measure. This was also possible within the filtered measure menu/pop up , but the results are pretty much the same.