4 Replies Latest reply on Mar 5, 2019 5:37 AM by Norbert Maijoor

    Monthly and daily aggregation

    Albert Alaluf

      Hello everybody,


      I have several questions about the project I'm working.


      The requirements are;

           4 years report, monthly aggregated

           4 years report, daily aggregated

           2 years report, monthly aggregated


      All they use same tables, similar or different metrics, means common or different formulas


      I started with the first report, wrote all queries in SQL, aggregated in the server side, wrote the additional formulas, finished the work and duplicated the work into another tableau file.

      The second and third report, I just changed the aggregation methods in the query, added additional formulas,

      Combined all these three files into one Tableau file, job done.


      At the end of the day, I have multiple dashboards, using these three different datasets.


      Looking for the file size, it is very small, I do not have any issue with this. Extracting the data is taking a little bit longer, around an hour, most likely is spending time with the database server to aggregate. It is not a very big deal, it will run once a day, however unnecessary job to make the work 3 times.


      My first question is how to optimize this work? All is working thru extract perfect, however, the formulas, are duplicated per the nature of the design.


      Every metrics in its own set used as ATTR, to go from daily to monthly, I just changed to SUM, this gave me a nice flexibility.


      I also like to hear your suggestions to name the formulas correlated with the dataset(s).


      The work now completed, rushed a little bit. But I'm not very comfortable with the complexity. I like to make it simple.


      Thanks in advance for your suggestion


      A happy new year and merry Xmas.

        • 1. Re: Monthly and daily aggregation
          Andrew Bickert

          Hi Albert,


          It is an interesting question. We have ran into similar situations where we are essentially make the same report over and over for slightly different aggregations. We never had to make multiple data sources though as we used Level of Detail expressions along with Dynamic Filtered ranges per sheet to show all the data on one dashboard with different date ranges/metrics. IE: on one dashboard, we have a rolling 3 year average compared to the last 6 weeks rolling and then also have a Year To Date on the same dashboard. Not sure if this is entirely similar but I do agree to simplify and not have excess extracts running is the way to go.



          1 of 1 people found this helpful
          • 2. Re: Monthly and daily aggregation
            Norbert Maijoor

            Hi Albert,


            I was triggered by your reply;) and trying to understand your challenge.


            Upfront I making the following assumptions.


            All required information is stored in one table and all information is available on a lower level then day.


            Please validate if the following approach  is applicable in your scenario.


            1. Create one datasource based on the table (datasource-table do have a 1:1 relation)


            2. Data in the datasource is available on the lowest level. At the same level as in the underlying table


            3. Define two new aggregated objects on Day and Month/Year level


            4. Define sheet 1 filtered on the last 4 years at the level of Month/Year with metrics A,B,C


            5. Define sheet 2 filtered on the last 4 years at the level of day with A, B


            6. Define sheet 3 filtered on the last 2 years at the level of Month/Year with B,C D


            6. Store both sheets at different aggregated level (Day, Month/Year) on one dashboard


            Let me know;)




            1 of 1 people found this helpful
            • 3. Re: Monthly and daily aggregation
              Albert Alaluf

              Hi Norbert,


              This is exactly what I did and it worked perfectly.




              • 4. Re: Monthly and daily aggregation
                Norbert Maijoor

                Hi Albert,


                Glad it worked out. Thanks for the 'Badge'. Much appreciated:)