Forum Discussion
Date filter in pivot tables
Hi,
We have a scenario where we want to find the customer balance by a given date. Such balances can be based on a table similar to the following table:
| CustomerNo | Voucher | Date | Amount |
| 1 | 1000 | 28-11-2020 | 1876 |
| 1 | 1010 | 02-12-2020 | -1876 |
| 2 | 1001 | 15-12-2020 | 1586 |
| 1 | 1002 | 05-01-2021 | 189 |
| 2 | 1001 | 15-01-2021 | -1586 |
| 1 | 1003 | 10-02-2021 | 731 |
| 2 | 1004 | 10-03-2021 | 934 |
| 1 | 1005 | 10-04-2021 | 1354 |
When we pivot the data, we can aggregate pr. customer and identify the balance per customer. Although there does not seem to be an intuitive way to set an ending date.
For example we want to be able to generate the following result. Lets say we want the ending balances by the 31st December 2020, we should be able to get:
| CustomerNo | Amount |
| 1 | 0 |
| 2 | 1586 |
Although in the pivot table we are only able to add dates filter as a slicer or a timeline. The slicer is not usefull as you need to select all dates before the filter date and when using the timeline you have to scroll through all dates up until the filter e.g., we have to select from January 2016 until December 2020.
The latter is not an efficient approach as it is also prone to be making mistakes.
Does anybody have a suggestion to making a filter that basically just allows for setting a 'before ...' parameter?
We cannot filter directly on the underlying data as we build the pivot table on a cube, hence if we add the dates filter to the table, we get all entries, and if it is added as a filter, we are basically back to just having the slicer, where all values must be selected.
Please let me know if you need more details.
Best regards
Jonas
- Hi both of you
Thank you for the input! I did not end up using it, but might for other scenarios.
I found that the best solution was to insert the timeline filter in the pivot table. Despite the timeline filter to be a bit chunky to work with, I figured that it created a relationship between the data and the selections in the pivot table to create the end balances per customer.
When the timeline filter it added is was able to hover over the date column (in the PivotTable Fields) and then filter on date without actually adding it as a filter or a value in the pivot table, although it appears to interact with the timeline filter and then manipulate the query in that way.
Thank you for the effort
Best,
Jonas
5 Replies
See the attached. I use the relatively new Dynamic Array functions UNIQUE and FILTER in this.
You can find these functions explained on this YouTube video. https://www.youtube.com/watch?v=9I9DtFOVPIg
Basically this combination allows you to "create" your own cross-tabulated table, similar to the result that a Pivot Table creates, but the FILTER function can be written to work with such things as variable ranges of dates.
- Hi
Thank you for the reply! It appears to be working, although the date in the cube has to be manually transformed to a date type.
Although there are no opportunities to make this operation in the pivot table instead of having to make a pivot table with all data and then apply the functions? As this may be equally cumbersome as using the timeline filter
Best
JonasThank you for the reply! It appears to be working, although the date in the cube has to be manually transformed to a date type.
I'm not sure why it's a problem to treat a date as a date type, rather than as a text string that looks like a date but is really a text string. Yes, there may be a need to convert it, if the data are coming from another source, but it just makes sense. Doesn't it?!
Although there are no opportunities to make this operation in the pivot table instead of having to make a pivot table with all data and then apply the functions? As this may be equally cumbersome as using the timeline filterAs I said in my original post, I've found it to generally be the case that I can use UNIQUE and FILTER and obviate the need for the Pivot Table altogether. FILTER enables the creating of very sophisticated criteria for which rows of data get summarized, which is what you're trying to do.
