Mar 22 2020 08:20 PM - edited Mar 23 2020 08:41 PM
I have a date column in my Pivot table. I use a timeline to select the dates. However, this is a bit clumsy.
I'd like to show fields for start date and end date. How can I then use those values as a filter in my pivot table?
What I am trying to do is to allow others to modify the results. I just want them to select the start date and end date and refresh the pivot table for updated results.
Attaching a file which shows what I am trying to do.
Mar 23 2020 07:45 AM
It depends to a certain extent on how you intend that filtering to work. By that I mean, you could be wanting to filter on absolute dates; or you could be wanting to filter on extent of time between two dates, regardless of the absolute dates of the start and finish. And maybe there are some other possible combinations, but I'll use those two to illustrate at least two possibilities.
Now, if none of that makes sense (or you don't see how to do it), please upload a sample of your file so somebody here can see the actual layout of your data.
Mar 23 2020 03:01 PM
@mathetes In the timeline control, I select all days from Monday to Friday of a week.
The question really is how to use variables in a pivot table, without the user having to select values in a timeline or slicer.
I'll post a file later.
Mar 23 2020 04:47 PM
As variant
Let say you have range of dates and source table
If range is not a table let add it to data model through PivotTable. Let build main PivotTable with adding it to data model.
In data model add calculating column to main table
and use it as the filter.
Mar 23 2020 08:32 PM - edited Mar 23 2020 08:42 PM
Here is a file.
Also here is what I am trying to do:
What I am trying to do is to allow others to modify the results. I just want them to select the start date and end date and refresh the pivot table for updated results.
Mar 24 2020 05:19 AM
I'm going to confess my own ignorance here: I'd never seen that Timeline capability before. [I began using Excel (well, Lotus 1-2-3 actually] back in the late '70s, early '80s. Used to read the manuals, and was for the time a "power user", up until when I retired in 2002. Excel has introduced a LOT of new features since then, and Timeline (as you've deployed it here) is one of them.]
And I think it--Timeline-- looks amazing, and easy to use. And I'm going to be looking for opportunities to use it myself.
So although it might be easier to let users select a start and end date, I'm not convinced you'd be doing them any favors.
Unless, that is, one of your mandates is to be less flexible than the Timeline allows. You mention selecting a standard work week, Mon-Fri. IF you only want to display results for any given Mon-Fri period, I'd suggest using one or more of the date functions to create a "helper column" that reads from 1 to 52, so that the data for any given day gets funneled into the appropriate week, and use that WeekNum column as the filter your users can select.
I'm making a couple of assumptions here: (1) the actual spreadsheet you're working with contains far more granular data than you show in your example; (2) you're enough of a power user that you don't need more help in developing those supplemental columns that either Sergei or I have suggested.
Obviously, though, come back with further questions or comments as you have them.
Mar 31 2020 07:08 AM
In connection with another thread, I've come across the linked YouTube video which explains how to accomplish what you are trying to do. It's tricky, but effective.
https://www.youtube.com/watch?v=sHfWRb2yUrM