Multiple complex filters

[paulette33]

I have a report that brings in This Year's Revenue and Last Year's Revenue and then does a comparison between the 2. The report is laid-out as a crosstab. In the comparison section, I am also calculating a YTD difference, which is bascically a running sum of the difference. The final output of the report is to only display data where the comparison (comp revenue) or the year to date comparison (YTD comp) is a negative value. In my report, I've created a complex filter on the comp reveue. However, I can't filter the YTD comp. I get an error message that says "Cannot use aggregates in complex filters". Any ideas on how to get around this?

Thanks in advance for your help.

 

[skrandel]

Build an intermediate variable.

Flag = if (<YTD Comp> < 0) then 1 else 0

Then do a ranking on one of your dimensions and base it on <Flag>. Show the top 1. It will show the top 1 and all ties, so you should see exactly what you want.

Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com