Excluding records in chart based on expression

[#1]

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.

Any ideas?

Thanks for your help.

[prieper]

Sorry,
but I do not understand your question?
What do you want to achieve?

Peter

[#3]

Sorry, gues I wasn't clear enough.

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?

Hope this makes a little more sense.

Thanks.

[#4 · Accepted solution]

how about this in your Cur Value expression?

if(Column(1)+Column(2)>0,sum({$<Date_Value=>} if(Cur_Date = EnterDate, Cur_Value)))

[#5]

whoops, just noticed in your second chart you have an equivalent expression. So I guess I don't understand the question.

[#6]

Hi dragonauta,

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.

Thanks.

[prieper]

Hi,

apart from the formula, have you tried to change under "Total Mode" to Sum of Rows?

HTH
Peter

[#8]

That's it! Sometimes the answer's simple and right in front of you but you can't see it. Thanks.