Your underlying data was a little difficult to digest. Is there a reason you don't just have a date field in addition/instead of having these different date component fields (which mostly appear to be text fields)?
I think what you are describing would also be quite easy to accomplish if such a date field existed in your data.
The way your data exists presently, I think you are asking for a concatenation of the Year+Month+Week when Week is selected, Year+Month when Month is selected and Year when Year is selected in the report filter parameter.
I have attached a packaged workbook doing this.
The change I made was to make a calculated field that depended on the parameter value selected.
I called it x-axis.
Hope this helps!
Test_V2.twbx.zip 36.6 KB
1 of 1 people found this helpful
I think I have a sense of your goal: When Week is chosen for the Report Filter, then the filters for Week, Month, and Year should be available. When Month is chosen, only Month and Year, then when Year is chosen, only Year should be available? Is that correct?
I believe what I'm seeing is that even though the Year, Month, and Week filters are set to be global filters, they are being affected by what is happening when you swap worksheets, because the "instance" of the quick filter is tied to a specific worksheet. When that worksheet is removed from the display by the parameter, that instance "loses" the information.
Here's the simplest workaround I can think of: Since your time dimension is already broken out into different strings, instead of swapping out worksheets create a calculated field that will return a different string for the different parameter values. And instead of directly using the Month and Week values in the Quick Filter, create calculated fields for each that return different values based on the parameter, i.e. just one like "All" for Week when Year or Month are chosen, and the values of Week when Week is chosen.
It was the report requirement and have table in that way for aggregation.
I have managed to create report now. Just added one dummy sheet and added all required filter in that sheet. Added same sheet in dashboard and added all filters in dashboard page from dummy sheet. It is working as my expected way.
Attached updated report here.
Test_V2.twb.zip 16.5 KB
I hadn't thought of that solution, that's great!