4 Replies Latest reply on Aug 13, 2018 12:50 PM by Amanda Paige Michaels

    Dynamic Filtered MAX([Date])

    Amanda Paige Michaels

      Hello all,


      Frequent reader and first time poster. I've scoured the forum and I can't seem to find an answer to my problem.


      In trying to create a header bar that summarizes the KPI above a trend visualization, I'm trying to portray 4 things 1) The Total revenue within the time frame chosen 2) The previous weeks* revenue 3) this week's revenue 4) a variance dot that shows the trend between the last 2 weeks*.


      Originally, I have the "This Period" and "Last Period" formulated in the following way:


      This Period End: {fixed:MAX([Date])}

      This Period Start: [This Period End] - 7


      Last Period Start: [This Period Start] -1

      Last Period End: [Last Period Start] - 7


      This Period Revenue: IF [Date] >= [This Period Start] AND [Date] <= [This Period End] THEN [Total Revenue] END

      Last Period Revenue: IF [Date] <= [Last Period Start] AND [Date] <= [Last Period End] THEN [Total Revenue] END


      However, I would like the user to be able to change the end date to something other than just the most recent date. Therefore, if they want to see what happened before 7/31/2018, they can change the slider so that it ends then and the summary bar will show the values representing 7/31/2018 - 7/24/2018 against 7/23/2018 - 7/16/2018 and so on.


      At this point I run into the error of comparing aggregate and non-aggregate values. I can't simply use a [Date] >= MAX([Date]) formula, but I can't fix it, because it would always reference the last date.


      I've attached my sample workbook. Any help on this problem would be greatly appreciated!


      *weeks is the period of time I specified for this sample workbook I created. Ideally, the user would be able to view this report on a monthly, quarterly, and yearly basis. This is a reason that I wanted to keep Tableau's Date sliding bar, rather than choosing Start and End Date parameters. The parameters have the unfortunate consequence of making it visually ugly when switching between a daily/weekly/monthly/quarterly/yearly view.