cancel
Showing results for 
Search instead for 
Did you mean: 

Transposing Pivot Table

amelia
8 - Cloud Apps
8 - Cloud Apps

Screen Shot 2022-02-16 at 1.43.17 PM.png

Current format ^ 

Screen Shot 2022-02-16 at 1.43.28 PM.png

 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 in the image above). However, the default in Sisense is to put the values in columns. Switching the rows and columns also does not seem to resolve this issue. 

 

I've attached the current format of the table I have in Sisense, as well as the desired format I am going for. Essentially, I want to recreate the format of the Tableau table in Sisense 🙂 

Thank you and please let me know if anything was unclear. 

 

1 ACCEPTED SOLUTION

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:

Angelina_0-1645192743441.png

I got this table: 

Angelina_1-1645192908003.png

- Angelina
angelina.bulgakova@qbeeq.pl

 

 

 

 

View solution in original post

5 REPLIES 5

harikm007
12 - Data Integration
12 - Data Integration

@amelia ,

Are you getting expected result when moving  the field 'Metric' under Rows panel?

Rows - Country, Metric

Columns - Year, Quarter, Month

hi, thanks for your reply 🙂 the issue is that I don't have a field called metric - instead, i have many different calculated fields that i would like to group together into a field called metric, so i can format them like Tableau's table. An example of what I want it to look like it like this: 

Screen Shot 2022-02-16 at 2.20.20 PM.png

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:

Angelina_0-1645192743441.png

I got this table: 

Angelina_1-1645192908003.png

- Angelina
angelina.bulgakova@qbeeq.pl

 

 

 

 

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 🙂

Screen Shot 2022-02-18 at 12.37.37 PM.png

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:

Angelina_2-1645510780649.png

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

Angelina_1-1645510742019.png