Filtering straight table based on expression value

[#1]

I have a straight chart with one dimension (client) & three expressions: (1) yesterday's sales, (2) 20 day avg sales, (3) difference in the two. I need to create two tables: one that will show only positive differences & one that will show only negatives.

I have been able to filter straight tables for positive and negative values in the past by using an if() statement in the dimension field (ie if(sum(sales)>0,sum(sales)) ) but this doesn't work with multiple expressions.

Any ideas of how to filter out those clients that don't meet the straight tablecriteria? Thanks for your help!

[#2 · Accepted solution]

You should be able to put this logic into your expressions and then use the Suppress Null Values to only show the ones you want.

If(YesterdaysSales-20DayAvg>0, YesterdaysSales)
If(YesterdaysSales-20DayAvg>0, 20DayAvg)
If(YesterdaysSales-20DayAvg>0, YesterdaysSales-20DayAvg)

Use something like that for each of your expressions. When the difference is negative, the values of these expressions will be null and that record will be suppressed. You end up with only the records you want.

Replace YesterdaysSales and 20DayAvg with the expressions you are currently using.

[#3]

thanks! that did it.