8 Replies Latest reply on Sep 21, 2018 6:57 AM by Joao Goncalves

# Hide dates but keep them in the data set

I am trying to create a bar graph that will show the value for just for January.  But in order to make this calculation a running total is required.  If I use a filter on the data the running total will be wrong.  But without the filter I see values for the whole year rather than just January.

YTD Route/FTE sheet shows the correct value (2070.7) for January however it also shows the rest of months in the year as well.

Calculations are as follows:

Route/FTE YTD: RUNNING_SUM(SUM([Number of Records])) / RUNNING_SUM([Productive Hours].[Total FTE])

Data for the calculations can be found on sheet 25.

I need to figure out how to create a parameter that will allow me to select month (January, February...) to show.  But the underlying data from the whole year will still be used in the running sum.

Thank You!

• ###### 1. Re: Hide dates but keep them in the data set

Brittany--

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:

1. Create a calculation using the following formula: last()=0
2. Add this calculation to rows to ensure that True shows up for January
3. 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,

Dan

3 of 3 people found this helpful
• ###### 2. Re: Hide dates but keep them in the data set

Thanks Dan!

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.

• ###### 3. Re: Hide dates but keep them in the data set

The following calculation should do the trick:

lookup(attr(datename('month',[Dispatch Date])),0)

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,

Dan

• ###### 4. Re: Hide dates but keep them in the data set

Thank you! That worked perfectly.

• ###### 5. Re: Hide dates but keep them in the data set

Hi,

I tried doing above but it doesn't work for all the values in the columns (please see below) , I used the following formula: lookup(attr([Days to Term Start]),0) >-15. What am I doing wrong?

Thanks!

• ###### 6. Re: Hide dates but keep them in the data set

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?

• ###### 7. Re: Hide dates but keep them in the data set

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.

• ###### 8. Re: Hide dates but keep them in the data set

Did you find any better solution?