Filter pivot table with periodOverPeriodPercentDifference value for last date

Q&A
, ,
1

Hi!

I’m struggling with filtering pivot table by last periodOverPeriodPercentDifference value.
I have a table like this

Screenshot 2025-09-23 at 13.59.30
Screenshot 2025-09-23 at 13.59.302520×304 32.8 KB

In my scenario all amount values are actually created with calculated field like this

sumOver(
  sum({amount}),
  [{group}, truncDate(‘MM’, {date})]
)

and percent_difference values are created with periodOverPeriodPercentDifference

What I’m trying to do is create a filter, which would keep only the groups (with all months values) if last_month percent_difference is, for example, below 0.
So in the case above, this filter should keep only name_2 values (again, with all months).

It seemed trivial, but turned out to be surprisingly painful.

Thank you!

2

Hi @Valentin,
So when you try to setup a filter based on the periodoverperiod field, how did you try to set it up and what was the outcome?
If you could provide a screenshot of the filter setup you tested, that would be helpful as well!

3

Hi @Brett

Thank you for your reply!

I have tried different approaches, but the result is always pretty much the same:

Screenshot 2025-09-24 at 12.45.10
Screenshot 2025-09-24 at 12.45.102514×362 39.5 KB

In field test_filter_value(max) I see ‘1’ only in last month (if percent difference in last month is below zero) and I need to, I guess, pre-calculate this filter to show ‘1’ for all values if last month’s percent_difference is below zero.
test_filter calculated field now looks like this

ifelse(
    ({amount_pre_agg_this_month} - {amount_pre_agg_previous_month}) 
    / 
    {amount_pre_agg_previous_month} < 0,
    1,
    0
)

What I think I actually need to get is this:

Screenshot 2025-09-24 at 12.54.59
Screenshot 2025-09-24 at 12.54.592512×366 39.9 KB

So when I add filter ‘test_filter_value’ (aggregation - max) equals ‘1’ I would see all the months for group if last month percent_difference is below zero.

4

Hi @Valentin,

Thank you for the detailed breakdown; so when you add a filter for ‘test_value_filter’ to filter out if it equals zero, that does not remove the whole group?

To get the outcome you’re looking for in the second visual picture, where you have 1’s across the entire row, I believe you would need to account for the max date in your ifelse, that way it would say something like if max date, calculation is less than zero, then apply a 1.

5

Hi @Valentin

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

6

Hi @Valentin

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!