Crosstab: Suppress Zero (but only some zeros?)

[MarkEdmondson]

Morning All,

I'm working on a crosstab that has a string as the vertical axis and date (by month) as the horizontal axis. In the body of the crosstab I've got a formula (average of a numerical formula designed to calculate compliance).

Now all of this works just swimmingly EXCEPT when it comes to zeroes. In the results I've got two types of zeroes:

1) A zero where no audits were done that month, thus the compliance formula returns 0.00

and

2) A zero where audits were returned but they were all negative, thus the compliance formula also returns a 0.00

Is there anyway within the crosstab I can differentiate between the two?

I'm guessing that supress zero formula area is what I've got to use, but I've no idea where to begin.

Thanks

 

[fisheromacse]

i would suggest creating a formula (ie: {@ShowZero})that would evaluate your data and return a 1 if there were audits that were all negative and a 0 if there were no audits, then you can suppress based on those the values of the {@ShowZero} formula.

 

[MarkEdmondson]

That sounds perfect, would you mind providing a little bit more detail as to how I could implement?

Thank you so much.

 

[MarkEdmondson]

bumpers

 

[fisheromacse]

Now that i think more, i am not sure how this would work for the values in a crosstab. Hopefully someone else will chime in with a better idea.

what i was originally thinking would use a formula like:
IF Count({Table.Audit,{Table.date},"monthly")=0
THEN 0
ELSE 1

then use the results of the above formula as your suppression criteria.