3 of 3 people found this helpful
If you are always concerned with seeing the most recent month--in other words the last entry in the table--you can do the following:
- Create a calculation using the following formula: last()=0
- Add this calculation to rows to ensure that True shows up for January
- Move this to the filter shelf and keep the True value
Since this calculation is a table calculation, we are hiding the previous months rather than filtering them. This allows us to keep the data for the previous months hence why the running sum still works.
Regarding the last() function, all this does is find the offset of the current row from the last row. In other words, how many rows is the current row away from the last row. In the case of the last row--or the most recent month--this will always be 0 hence True being returned as the result.
Hope this helps,
That is helpful and I might be able to use that as a temporary solution.
Ideally I would still like to be able to choose a specific month.
The following calculation should do the trick:
This calculation just lookups the month within each row of the table. Again, since this is a table calculation, we hide instead of filtering. If you add this to the filter shelf and then expose the quick filter, you and your users should be able to dynamically hide everything but the selected month.
Hope this helps,
Thank you! That worked perfectly.
I tried this, it works for rows but does not work for reference lines. For example if I want to get historical max, then this "filter" would still restrict the data that the reference line query. Any solution around that?
I found another issue with this method. If you have Total or Grand Total then those would still be displayed. I wonder if there is a better solution to this.
Did you find any better solution?