Table showing 1 month and avg of 3 months

  • 9 February 2017
  • 5 replies

I’m trying to create the following table:

Category | Dec 2016 | 3Mo Benchmark

A | 200 | 230

B | 145 | 189

C | 235 | 300

My approach is the following -

filter: 5 months ago for 4 months.

Pivot above table by Month and got the following header:

Month | Sep | Oct | Nov | Dec | BM (table calc)

BM is equal to average of (pivot_index(events, 1) + pivot_index(events, 2) + pivot_index(events, 3) )

But I can’t hide Sep, Oct, Nov results so that only Dec and BM columns are showing.

Is there a better way to get the top table? thanks.

5 replies

Userlevel 5

Hi @Alice_Ching - there is currently no way to hide individual pivot columns. However, there is a workaround - we can use pivot_index(events, 4) to pull out the Dec column as its own table calc. Then you can hide events so that only new Dec calc and BM columns are showing!

Great, thank you. Is there a way when downloading the table in Excel format that the hidden from visualization parts can be excluded?

Userlevel 2

Hi @Alice_Ching - currently it is not supported but I’d be happy to pass along your request to our product team!

ok great, thanks.

Userlevel 3

@Alice_Ching if you really want to hide the Sept-Nov columns from the download, and if “Last month total” and “3Mo Benchmark” are commonly used enough to justify adding to your model, you could build also build them as filtered measures in LookML.