I try to make a report indicating sales by product/by month. I want to include in the crosstab an alerter indicating if the product has been created (or deleted) durind the period. Let’s imagine a product 1 sold during all year and a product 2 created in March. My crosstab should look like that :
jan feb mar apr …
Product 1 qty qty qty qty …
Product 2 XX XX qty qty …
XX being the alerter (“non created”), replacing empty cells.
My alerter is
Value to compare : Month of Sales
Operator : <
Value 1 : Month of creation
Result : non created
If I put “0” when Month of Creation is empty, I obtain no alerter at all.
If I leave Month of Creation in a normal way (empty if the product was not created this year), I obtain :
jan feb mar apr
Product 1 XX XX XX XX
Product 2 XX XX
The reason your formula does not work is because you cannot mix and match the OUTPUT results. To get the result you are looking for, try something along the lines of:
=If < Then “Non Created” Else FormatNumber( ,“0”)
In this case, both of the results will return as text. I wasn’t sure what your NULL value was since you were simply attempting a “less than” comparison. If your QTY is the possible null, then try:
=If IsNull() Then “Non Created” Else FormatNumber( ,“0”)
You can, in the format cell dialog box, place a ualue in the “Undefined” box of the “Number” tab. You can make this say “[blue]Undefined”, to make it stand out more.
You only need to use variables for this normally if the Undefined value has some special meaning. To make it look different, just using the Cell Format box is enough.
Tom Tobin
Program Manager
Web Products, Business Objects
If your QTY is the possible null, then try:
=If IsNull() Then “Non Created” Else FormatNumber( ,“0”)