4 Replies Latest reply on Jul 31, 2011 5:01 AM by Teresa Obis

    YOY, MAT and YTD in the same worksheet

    Teresa Obis

      Hi all,

       

      I have some formulas to compute Year Over Year, Movil Average Total and Year to Date  Sales, Growth and Share but I do not know how to put all these information in the same worksheet.

       

      Find attached an example about how I do it now. I use one worksheet to put YOY, another to MAT and another to YTD and then I have to put all togheter in a dashboard. But it is a hard work to do with every table I would lilke to do.

       

      Can anyone tell me if there is another way to do it?

       

      Thank you very much

       

      Teresa

        • 1. Re: YOY, MAT and YTD in the same worksheet
          Joe Mako

          Here is one approach for getting the tables of numbers as one worksheet, since you have eight worksheets, they can be turned into one worksheet by duplicating the data eight times. An easy way to duplicate data is with a many-to-many join. In the attached Excel file, you will see two worksheets, "Hoja1" should look kind of like your source data, but with an additional "Join Key" column with the same value on every row, and the "Dimensions" sheet has eight rows, a row for each of your original worksheets. Then these two tables are joined on the Join Key, the data in Hoja1 will be duplicated eight times.

           

          In the attached, I made use of your calculations, but did not create a set,

          • 2. Re: YOY, MAT and YTD in the same worksheet
            Teresa Obis

            Joe,

             

            Your solution is wanderfull, as usually they are.

             

            Now I am boring about performance. I have a 9 millions rows data. I have to roll up to month to let Tableau draw tables and graphs in a reasonable time. But if I have to multiply my data by the number of variables I would like to put together in a Row Dimension and in a Column Dimension I will loose efficiency.  For example, now I am desinging a dashboard that has in the Row Dimension 4 variables (Total, Category, Family and Form) and in the Column Dimension (12 months, YOY, MAT and YTD). My data will be 16 times my actual data set. That will not work!!!

             

            I proupose an improve in the "Wouldn't it be nice if.." forum:

            - "Create a table with two o more dimension variables CONCATENATED (not Nexted)"

             

            You have identifyed very well the problem. Your solution of "Row Dimension" and "Column Dimension" will be fantastic but better if Tableau's people consider it as an improve for new software releases. What do you think?

             

            Teresa

             

            PS: Please I have a problem with a small variation of Numeric Distribution. Could you see my post and help me.

             

            Thank you very much.

            • 3. Re: YOY, MAT and YTD in the same worksheet
              Joe Mako

              > 9 millions rows data. I have to roll up to month to let Tableau draw tables and graphs in a reasonable time.

              I highly recommend creating an extract, using the Tableau Data Engine. You will no longer have to roll your data up, and you can easily multiply your data x4 making the .tde file with 40 million rows, and I would still expect near immediate response. It will take a few minutes for the generation for the .tde file, depending on your data source. When I say only a x4 size increase, I mean a duplication of the data for the columns (12 months, YOY, MAT and YTD). Those are the difficult setup, and then just duplicate the worksheets, and make one for each row set, kind of like what I did on the dashboard "Dashboard Brands and Total" in the above.

              • 4. Re: YOY, MAT and YTD in the same worksheet
                Teresa Obis

                Joe,

                 

                I do not know what I am doing bad. My original data is an extrat, tde file. IT people generate it to me from SQL Server. It weigths 987067 KB (987 MB?) I do not know exactly how many rows it is (I do not know how to get this information). But before I roll up to month I must wait more than 10 minutes to change from a dashboard to another, when I have around 5 dashboards similars to the one you see in Brands and Total. And Tableau Support Team tell me that I must roll up data.

                 

                Your idea of create a Time dimension to be able to put in the same table: Month, YOY, TAM and YTD is very interesting. In fact, the time I have to wait to refresh a dashboard is proportional to the number of tables it contains (I think so because it has to do a Query for each table), so problably I will have better performance if generate a x4 tables and include time dimension in a single table.

                 

                The problem is that I do not know how to do it. My original data is a tde (not an Excel File), so I do not know how to join the dimension4 sheet you send me, with my Month roll up data. It is easy to Join several sheets from the same Excel File, but when I have one tde file and one Excel file, how can I join these two data?

                 

                Sorry for my ignorance, I am so new in Tableau,

                 

                Teresa