Forum Discussion

amelia's avatar
amelia
ETL
02-16-2022
Solved

Transposing Pivot Table

Current format ^   The format I want to achieve ^ Hi everyone, I am currently working on a project and the client wants the values to be displayed in rows, grouped by Country (as seen i...
Blox
Information Technology Analytics
  • Angelina_QBeeQ's avatar
    Angelina_QBeeQ
    02-18-2022

    You could create a small table in your EC for metrics' names.

     

    select 1 as key,1 as id, 'metric 1' as metric
union all
select 1 as key,2 as id, 'metric 2' as metric
union all
select 1 as key,3 as id, 'metric 3' as metric

     

    Key is created for relationship with any needed table in EC. And in that table, we also add the custom field 'key' with 1. It allows duplicating rows for each metric in the pivot table.

    The next step is creating a formula for the pivot table:

     

    case when avg(id)=1 then Sum(metric 1)
when avg(id)=2 then Sum(metric 2)
else Sum(metric 3)
end

    For example, for this set of data:

    I got this table: 

    - Angelina
    [email protected]

     

     

     

     

amelia's avatar
amelia
ETL
02-18-2022

Thank you so much, this really helps! Can I ask, I would like to add a section below, for the totals of all the metrics. It will be called Grand Totals. Do you have any idea how to achieve it? Once again, thank you 🙂

Angelina_QBeeQ's avatar
Angelina_QBeeQ
ETL
02-22-2022

In this way, you could create a custom table and duplicate your data like this:

select Country,
Date, Sales, Amount, Key from [Metrics]
union all
select 'Grand Total' as Country,
Date, Sales, Amount, Key from [Metrics]

 And the result:

Also, you could swap rows in the pivot table and add subtotals: