Filtering on the result of a calculated field

[Phoebe]

Hello!

I have spent a couple of days (no joke) trying to get a filter to work.  I have two calculated fields in a list query.  They are called totalFY and countFY, respectively.  Here are the expressions for the fields:

totalFY: total ([Hard Credit] for [Constituent ID], [Date of Record Fiscal Year])

countFY: count (distinct [Date of Record Fiscal Year] for [Constituent ID])

When I drag totalFY to the detail filters panel, and set a threshold (let's say, >5000), my list query turns out the expected results, which would be customers whose total purchases for each year are greater than 5000.

But when I add the countFY field to the filter panel, it is completely ignored by the filter threshold I set (in this case, >2).  I've tried several things over the past couple of days and am really stumped.

Any thoughts would be greatly appreciated!

Thanks so much.

[RKMI]

Hi Phoebe,

Have you tried to set one filter(totalFY) for before aggergation and the other filter(count Fy) for after agg? If this doesn't work can you tell what does the count distinct column (output result) look like when you set only one filter(totalFY) and disable the second filter.

Thanks,
RK

[Phoebe]

Hi RK,

I have tried setting it to "after aggregation" and that didn't help.  When I include only one of those filters at a time, they work as expected.  It's when I try to use them both at once that it ignores me.

[RKMI]

Hi,

Can you try to do a subquery apporach. Where you take a new query and use the output of the first filtered query(TotalFY > 5000) is input for the new query. Then apply the second filter( countFY>2) that way it sets out an order of precedence for filter request and use the second query as your output result for your objects(list, chart,...). Try if this works...

Thanks,
RK

[Phoebe]

Hi RK,

Thanks for the suggestion, that worked! 

Much appreciated.