Is there a way to exclude records from appearing in a chart based on the result of other expressions in that chart and not have that records values included in the chart totals?
In the example I attached, I want to exclude the item "77777" when the EnterDate is 4/30/2009 because that record has 0 for the "Avg Investment" and "Sales" expressions. I guess because the chart is using a Set Expression to look for all records that have a value that match the EnterDate it is including item "77777". If I try to exclude it in an IF statement, the chart does not show the item but the value is still included in the chart total.
In the chart labeld "No Exclusions" item "77777" appears because it has a Cur_Value for EnterDate 4/30/2009. I only want to see records in the chart that have both "Avg Investment" and "Sales" <> 0. Item "77777" does not have either value but appears on the chart. How can I exclude it from the chart based on the values in the other expressions?
I'm not sure how else to say this. The chart "No Exclusions" shows item 77777 with Avg Investment and Sales of $0 and a Cur Value of $50. It appears in the chart because I want to see the Cur Value for that date (the date entered in the EnterDate input field). But I don't want to include items that have an Avg Investment and Sales of $0 (which is item 77777). In the second chart (with the IF exclusion) I was attempting to exclude item 77777 using an IF statement. Something similar to your earlier suggestion (although yours is much simplier). It accomplishes the goal of excluding item 77777 from showing in the chart however, the Cur Value of item 77777 is still being included in the chart total. If you look at the two charts they both show a total Cur Value of $1582. If you add up the Cur Value amounts in the second chart (the one with the IF exclusion) the individual amounts don't equal the total. That's because it's still including the $50 from item 77777. I want to keep the item off the chart and not have it's amount included in the chart total.
I hope this clears it up a little. Or maybe I'm completely missing something else.