6 Replies Latest reply on Sep 26, 2017 1:45 PM by Joe Oppelt

    Aggregate a metric for a period of time


      Hello. I want to make a calculated field that takes the average of a metric over the past seven days. I then want to create another calculated field that averages the same metric over the last 91 days. Advice? Thank you.

        • 1. Re: Aggregate a metric for a period of time
          Joe Oppelt

          Do you have data on every day?  If so, then WINDOW_AVG(SUM(metric)),-6,0) will average the past 7 days.  But if you don't have data on all days, that will go back 6 "hops" in the table, which might go back 7 or 8 days (for instance, if you don't have data on weekends) and you might not want that.

          • 2. Re: Aggregate a metric for a period of time

            Wonderful! Thank you. I have data for every day.

            • 3. Re: Aggregate a metric for a period of time

              actually, this did not work. I was hoping to have these new metrics as stand alone in order to create a bar chart. but despite the change in days I still get the same result.

              • 4. Re: Aggregate a metric for a period of time
                Joe Oppelt

                Upload a sample workbook.  We might not be talking about the same thing.  Tell me what you see, and what you expect to see in the workbook.

                • 5. Re: Aggregate a metric for a period of time

                  My ultimate goal is to recreate these the chart on the left in Tableau. The single points represent the CIR for the past 7 days. While the horizontal lines represent the CIR over 91 days. The metric (attribute) CIR is called 'Calls Value.'



                  • 6. Re: Aggregate a metric for a period of time
                    Joe Oppelt

                    (V 10.1 here)


                    See attached.


                    In Sheet 10 I put the two SUMs so that I could see what's what.


                    I created some calcs.  See [Last Day in data].  This just tells me what the last day is.  Now I can display the results on just the last day (assuming you just want to see this calc for the last day.)


                    I made copies of your 7 and 91 day calcs and added a check only to do this on the last day.  Actually, this isn't necessary.  Tableau would calc this for all days.  I displayed these values on Sheet 11.  (I also filtered just to Cleveland for the sake of simplicity.)  Scroll all the way to the end to see the numbers.  If you want, throw your version of your calc onto text on Sheet 11 and you'll see that it calcs for all days.  (This effectively gives you a moving average along dates.)


                    Note:  You have to have [Date] on the sheet so that the table calc has the concept of days to work with, and look back 6 days, or 90 days.


                    On Sheet 12 I filtered so that we only show the last day.  Look at [Last index] to see how I did that.


                    On sheets 10, 11, and 12, Tableau's default of TABLE(across) to evaluate these calcs works properly.  Now go to Sheet 13.


                    Here I moved [Date] from COLUMNS to the DETAILS shelf.  I did this to take [Date] off the viz, but still keep it on the sheet.  (Remember, we have to have it on the sheet to keep the concept of days in the calc.)  This isn't COMPLETELY necessary.  If you go back to SHeet 12 and right click on [Date], and uncheck "Show Header", you'll see the date value disappear from the sheet.  But for demonstration purposes, I moved it to DETAILS on 13 to show you something about table calcs.  Go back to 13.


                    Right click on [Last index] in the filter shelf, and select "edit table calc".  Here, because [Date] isn't on the viz any more, I have to specify to Tableau how it needs to walk the table correctly for my purposes.  (Look at the same on Sheet 12 and you'll see that it's doing TABLE(across).)  By default on 13, tableau chose TABLE(down), but I need it to go down the list of Sites, and then  go across the list of days, and to restart for each site.  Here is what my settings look like:


                    I had to do that for [Last index] on filters, and for the two CIR calcs.  (for demonstration purposes I put the date value on TEXT here as well.)  Now I have the same values as I did on Sheet 12.


                    Go to sheet 14.


                    Here I left just the two CIR values on TEXT, and I took off the Site filter so that we see all sites.  Now you can format these however you want to display them.