See attached workbook for a solution. I added two calculated fields - one to determine the relevant period, the other to create a special filter that will only show relevant days but will keep the data for all days. Use parameter to set how many days back you want to show.
Works perfectly. Thanks Dimitri!
While these daily calculations are exactly what's needed in the text table, the grand total averages are a bit off.
Currently, the workbook is calculating a lifetime grand total average, however, I'd like to view the true average for only the visible days. To workaround this issue, I tried:
- Duplicating the date dimension to use with a particular measure (for example, Session Date - D7)
- Using the dimension as a filter (relative date - 8 days before the first visible table date)
- Adding the Session Date - D7 dimension to level of detail
- Editing the 'd7 table calc' Table Calculation in the level of detail
- Calculation: d7 table calc -> (advanced) Compute using Day of Session Date - D7, Day of Date - Session Date(copy)
- Calculation: d7 calc feeder -> (advanced) Compute using Day of Session Date, Day of Session Date(copy), Day of Session Date - D7
Though the daily values are still accurate, this approach is not producing the correct grand total average. Further complications arise when I try to add an additional duplicated date to the level of detail and filter (for example, Session Date - D5). Surely, there must be an easier way to accomplish the desired view. Any ideas?
I consulted the experts and got two suggestions:
- use parameters to control what goes into grand totals
- use page shelf (not ideal)
Both would be workarounds, and will add complexity. Do you want to give parameters a go?
Thanks for looking into this Dimitri. I'll try out suggestion #1.