1 Reply Latest reply on Jun 11, 2018 9:08 AM by Ken Flerlage

    Combining multiple calculated fields into one - Something like pivot (but not using Excel)

    Aaron Tan

      Hi,

       

      I have a set of data that I made some calculation to create a couple of aggregation that I would like to combine into a summary like pivot, except the issue I am facing is that I am using data from redshift and it's not possible to do Pivot directly from the data source.

       

      I have 3 main tables:

      Table 1 where I have total mins, table 2 is a mins viewed for show that comes with subtitle, and table 3 is the breakdown of the viewed with the respective language of subtitle.

       

      I created multiple calculated fields in order to get the following fields in my table 3 data source:

      The cells in blue are all calculated field:

      -Local, Other, Malay, Chinese are the type of subtitle categories from table 3, which I need it for graph labelling purpose.

      -Turned off is the total mins viewed from table 1 minus mins viewed with show that comes with subtitle from table 2

      -No sub is the mins total mins viewed with show that comes with sub from table 2 minus sum of all the mins breakdown in languages from table 3.

       

      All of these separate tables are 3 different data sources linked to Tableau and I need to pivot the table to get a result that look like the following:

       

      Pivot does not available for the data source that I am using hence I am now stucked with the multiple calculated fields and unable to build a summary pivot table. Can anyone show me the direction/approach that I can use?

       

      I am able to plot my charts with calculated from Table 3 but I am unable to build a 100% stacked bar chart using multiple columns as it ends with multiple 100%.

      With the data I am getting I am able to only build the following:

      But not:

       

      What I get is actually this instead:

      I got my 100% bar chart working by creating a separate calculated fields that produce the % for each language by each country, but the I am unable to show the actual minutes because the chart was built based on the % calculation instead of actual minutes which is needed to display.

       

      My end goal should look something like this:

      Sorry for all the censoring because the data is confidential, I am only able to create some mock data for now.

       

      Apologize in advance if my question is too long or confusing. Please let me know if there's something that is needed but I did not provide