Hi @Jesse,
I used this trick to hide rows in my pivot table and filter only visually.
It worked great, except 1 problem I have with Totals for some value fields.
This is how my table looks like:
The issue I have is that for the following fields:
$ increase vs. last year same month
% increase vs. last year same month
$ increase vs. last year (YTD comparisons)
% increase vs. last year (YTD comparisons)
It seems like the problem is only present on the Column Totals, while the columns grouped by category work as expected no matter what is selected in the Date Filter (defined as: minOver(min({Month of Adjusted Date}), [{Month of Adjusted Date}]))
However for totals, it only seems to work for the fields I mentioned when the months that are being compared to are also present in the table.
The formulas I am using for the fields are the following:
For Month:
sumOver(
sum({C Net Amount}),
[{Location Nickname}, {Month of Adjusted Date}, {CName Ordered1}]
)
$ increase vs. last year same month:
periodOverPeriodDifference(
sum({C Net Amount}),
{Month of Adjusted Date},
MONTH,
12
)
% increase vs. last year same month:
periodOverPeriodPercentDifference(
sum({C Net Amount}),
{Month of Adjusted Date},
MONTH,
12
)
Year to Date:
periodToDateSumOverTime(sum({C Net Amount}), {Month of Adjusted Date}, YEAR)
$ increase vs. last year (YTD comparisons):
periodOverPeriodDifference({C YTD$}, {Month of Adjusted Date}, YEAR, 1)
% increase vs. last year (YTD comparisons):
periodOverPeriodPercentDifference({C YTD$}, {Month of Adjusted Date}, YEAR, 1)
Previous Year:
{C YTD$} - periodOverPeriodDifference({C YTD$}, {Month of Adjusted Date}, YEAR, 1)
Thanks in advance!