5 Replies Latest reply on Apr 3, 2018 2:02 PM by Eric Hammond

    Denormalized data display(chart)

    Joseph thomas

      I have to display 12 months of rolling price for various products for a company [ Based on the company filter ] . The product list is standardized.The months are rolling 12 month

      So last month  :  1_month - 03/01/2018 and 12_month - 03/01/2017

      So this month  :  1_month - 04/01/2018 and 12_month - 04/01/2017

       

      company
      product1_month2_month3_month --12_month
      1a$10 $20 $15 $10
      1b$10 $20 $15 $10
      2a$10 $20 $15 $10
      2b$10 $20 $15 $10

       

       

      We use this form of data because of very high number of rows  and it was taking too much time [ ie every company per product there could be 12 rows for rolling 12 month period .. 24 rows for rolling 24 month period]

       

      How do I display this on a chart? The axis should also display the date value if possible.

        • 1. Re: Denormalized data display(chart)
          Eric Hammond

          Hi Joseph,

           

          Regarding, "We use this form of data because of very high number of rows...":  The high number of rows can be difficult when reporting from a spreadsheet, but Tableau works best with that structure.  If you still have access to the original data with many rows then use that with Tableau for easier reporting.  If that isn't an option, here is a way to deal with these data as presented:

          • Drag Measure Names from the dimensions section of the data tab to Columns
          • Drag Measure values from the measures section of the data tab to the text tile on the Marks shelf.
          • Remove measures from the measures shelf that you don't want displayed
          • Click-drag measures in the measures shelf to put the months in the desired order

           

          Associating date values with measures names of 1_month, 2_month, etc., might prove difficult.

          • 2. Re: Denormalized data display(chart)
            Joseph thomas

            Thanks. The challenge is displaying it as a visual. This gives me mostly a "text tables"visualization. What I really need is a line chart which shows the ups and down per month per product per company ( rolling 12 months).

             

            I can go back to normalized rows.  But My volumes are two high ...

            Normalised row:  let's say 20k companies with 6 products with 12-month rows =  20000*6*12 = 1.5 million rows

            Vs

            Denormalized data: 20k companies with 6 products = 120k rows ( yes 12 additional columns)

             

            Let me know if there is a preferred volume ( i know it a silly question ) .

            • 3. Re: Denormalized data display(chart)
              Eric Hammond

              Hi Joseph,

               

              I like the way you think about moving away from text tables.  Yes - please use the normalized rows.  Then Tableau has ways to visualize those data without viewing thousands (or millions) of rows of text.  for example:

              • Drag the date field to the Columns shelf, and drill down to months.
              • Drag the date field to the Filters shelf, relative dates, last 12 months.
              • Drag the Amount field to the Rows shelf.  If this doesn't default to a line chart then select line from the Marks shelf.
              • Drag the product to the color tile on the Marks shelf, giving you a different line for each of the six products.
              • Right-click on [Product] in the Dimensions section of the data tab, and choose Show Filter; now products can easily be  included/excluded for review.

               

              Next: 20,000 companies is a lot to review; hopefully there are regions or managers or other ways to break up these data.  Regardless, a scatterplot can identify outliers to be reviewed.  On a new sheet:

              • SUM(Amount) on the x-axis (Columns shelf)
              • There is likely another relevant measure of interest that belongs on the Y-axis (rows shelf)
              • Drag Company to the detail tile in the Marks shelf
              • Change marks to circle

               

              Now put both of these sheets on a dashboard, and add a dashboard action so that whenever you click on a dot (company) in the scatter plot, the line-chart updates to display that company's MoM results for all six products.  Play with these examples and it should become more clear how you can use Tableau to visualize this much data.  I hope that the views above give you some ideas about how to move forward; your final solution is sure to look much different than the simple examples above.

              • 4. Re: Denormalized data display(chart)
                Joseph thomas

                Thanks. Normalized data may not be an option in this case.. Volume, performance, and data itself etc are few key reasons. Is it possible to work with Denormalized data like the format supplied? The issue is the date field. Can we create a date field and align the same with each one of the measures so that this behaves likes normalized data.

                • 5. Re: Denormalized data display(chart)
                  Eric Hammond

                  Yes, labeling a series of measure names as dates is an issue, and I'll expect that you will need to solve for a few other inconveniences as well.  Try this as a work-around: add a date field to the Columns shelf, make it a dual axis, and keep it synchronized.  You might need to add a measure for this dual axis to get the labels to show - if so then change the transparency setting to fade away the unwanted measure.