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 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. 

 

 

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]

     

     

     

     

6 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    02-16-2022

    amelia ,

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

    Rows - Country, Metric

    Columns - Year, Quarter, Month

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

      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: 

       

      • Angelina_QBeeQ's avatar
        Angelina_QBeeQ
        ETL
        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]

         

         

         

         

  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    Data Pipeline
    12-02-2022

    Hi amelia 
    I'm Benji from Paldi Solutions, nice to e-meet you (:

    In case this is still an open item I would highly recommend you to check out Paldi's Financial Table as it's an intuitive easy-to-use solution.

    One of the key features of this plugin is that you can present a transposed pivot table for users.

    The advantage of using this plugin is that you don't need to do any schema changes or dashboard\widget scripts which add more complexity and might limit you over time...


    Feel free to reach out of you have further questions, we're always happy to help (: 
    [email protected] 
    Paldi Solutions - Number #1 Sisense Plugins Developer