5 Replies Latest reply on Apr 26, 2016 12:11 AM by Shiva Prakash Y V

    Aggregate Various Measures over Last 7 days, Last 15 days and Last one month.

    Shiva Prakash Y V

      Hi All,

       

      I am stuck in creating a table where it displays the SUM(Sales), SUM(Profit) over Last 7 days, Last 15 days and every month. I could create them in Different work sheets but I need to view it as one table.

       

      Please find the image of a sample table required. sample.png

        • 1. Re: Aggregate Various Measures over Last 7 days, Last 15 days and Last one month.
          Patrick A Van Der Hyde

          Hello Shiva Prakash Y V,

           

          If the underlying data that Tableau is connected to is aggregated to the level of day then creating the dashboard solution you mentioned for the values for each of the time periods is the quickest and easiest way to accomplish your goal.  You do not mention why it is important to show these values in a single view but I encourage you not to be stuck with limiting your use of Tableau this way. The issue here is the data included in last 7 days is also the same sales data (and rows) included in last 15 days and last month.  This aggregation of sales data at different levels while also including this data in each bucket gets messy and is not how Tableau is designed to work with data. 

           

          If instead , you want to do some really cool comparisons of data from different time periods and dig in exlopring the data then Tableau is great for that.  See many of the examples here: Top 15 LOD Expressions | Tableau Software  for that sort of analysis.  

           

          If a crosstab of just these values is the must have requirement then I suggest keeping it in a tool such as excel where you can create unrelated aggregations in the manner shown. 

           

          Patrick

          • 2. Re: Aggregate Various Measures over Last 7 days, Last 15 days and Last one month.
            Jonathan Drummey

            Some questions:

             

            a) Are the sales numbers being updated every day (presumably yes)?

            b) Do you want the latest month to be the latest full month of sales or show results to date that would be showing partial months?

            c) What is the business question being answered by this visualization? (In other words, are they looking to see exact values of sales, are they looking for trends, are they trying to make comparisons, etc.?

            d) What will the users of this visualization do after they see this chart? (In other words, what is the next business question they will have, or what is the next action they will take?)

             

            Jonathan

            • 3. Re: Aggregate Various Measures over Last 7 days, Last 15 days and Last one month.
              Shiva Prakash Y V

              Hi Patrick Van Der Hyde,

               

              Sorry for a late response. Thank you for your advice and I appreciate your time to reply.

               

              I am right now using scaffolds to create aggregate measures over last 7 days and last 15 days. Also I am using the Tableau's feature for Months to see the aggregate. I am bringing this both adjacent to each other in the dashboard in order to make it look like a single view.

               

              Since I am doing it for different Data sources ( sql database), I believe this is hampering the Performance.

               

              Is there any best way to do the same ?

               

               

              Thanks,

              • 4. Re: Aggregate Various Measures over Last 7 days, Last 15 days and Last one month.
                Tableau kumar

                I would like to suggest the following approach,

                 

                1) Create a Parameter to pass the "No. of Days" of data you want to see on the view.

                Name: No. of Days

                Data type: Integer

                Allowablevalues: All

                2) Then create the following calculated Field

                Name: Last N days Sales

                Syntax: if datediff('day', [Order Date], today())>=0 and datediff('day', [Order Date], today())<= [No. of Days] then [Sales] end

                 

                3) Now Add the [Order Date], [Last N days Sales] fields onto the view.

                4) drag the [Last N days Sales] then select "Sum" then select "Special" tab, then select "non-null VCalues".

                5) Right click on "Parameter" (  No. of Days), now we can supply the interger data, based up on that data will be filterd on the view.

                EX: you can 10 or 15 or 30......

                 

                 

                Best Regards

                Kumar

                • 5. Re: Aggregate Various Measures over Last 7 days, Last 15 days and Last one month.
                  Shiva Prakash Y V

                  I need to see both last 7 days, last 15 days and all the months together. I do not intend to  allow user to use the filter to choose the [last N days]. I need to view all these data in one view. Is that possible ?