cancel
Showing results for 
Search instead for 
Did you mean: 

Grouping Column Headers

amelia
10 - ETL
10 - ETL

Screen Shot 2022-09-12 at 11.17.24 AM.png

For the pivot table widget, would it be possible to group columns together and add category headers for the metric columns like this? I have pivot tables with several metrics, so I would like to group them together. Ideally, I would like to do so without scripting 

1 REPLY 1

Angelina_QBeeQ
10 - ETL
10 - ETL

Hi @amelia ,
You can achieve something similar by adding a small table. The idea is similar to this one (https://community.sisense.com/t5/build-analytics/transposing-pivot-table/m-p/2274), but you will add in the table "category" field like this:

select 'Revenue' as measure, 1 as key,1 as id, 'group 1' as category
union all
select 'Quantity' as measure, 1 as key,2 as id, 'group 1' as category
union all
select 'Discount' as measure, 1 as key,3 as id, 'group 2' as category

The formula is the same as in the article:

case when avg([id])=1 then [Total Revenue]
     when avg([id])=2 then [Total Quantity]
     else [Total Discount]
end

In the table, you will need to add "category" and "measure" fields as columns and you will get the following result:

Angelina_0-1663067894599.png

But this method is suitable when you do not have too many measures.

Best regards,
Angelina