How to replace N/A values in Rules Editor with missing/null?

[abcuser]

Hi,
in TM1 v9.5 I have a formula in Rules Editor:
['PRICE']=['VALUE']/['QUANTITY']

but in some cases Quantity is 0, so divide by zero problem appears and data in cells get N/A values. When I use topCount function in Cognos BI v8.4.1 Report Studio it returns incorrect values, it assumes that N/A values are the highest values. To solve this problem I have changed formula in TM1 to:
['PRICE']=['VALUE']\['QUANTITY']

Replaced / with \, so instead of returning N/A values I get 0. OK, now topCount function in Cognos BI works fine, but bottomCount function (to get bottom n-members) returns 0 as a minimum member. Which is mathematically correct, but I want to ignore this zeros.

The funny think is if I use first formula then topCount is working fine and bottomCount is not working fine. But if I use second formula then topCount is not working fine and bottomCount is working fine. So I can't have both topCount and bottomCount functions in the same report.

Question: Is there any special value in TM1 like "null" in relational databases or like "#MISSING" value in Oracle Hyperion Essbase, so to tell TM1 that for this member value does not exists. Something like:
['PRICE']=IF(['QUANTITY']=0,#MISSING,['VALUE']/['QUANTITY'])

What is #MISSING or null equivalent in TM1?
Regards,
igorM

[paulsimon]

Hi

Sorry there isn't really such a function (well there is - UNDEFVALS but it is best avoided)

I don't know that much about Cognos Report Studio, however, assuming that it uses MDX then you could use the FILTER Function to Filter out Zeros and then apply the TopCount or BottomCount to the result of that. If you can't do this directly in Report Studio but if Report Studio can show a View containing a TM1 Dynamic Subset then you could put the MDX in to that.

The following is an example using the SalesCube in the TM1 PlanningSample application that ships with TM1. This is used to create a Subset called Bottom5Prices on the Model dimension

{ ORDER( {BOTTOMCOUNT( { FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [model] )}, 0)},
[SalesCube].([actvsbud].[Actual],[region].[Canada],[account1].[Price],[month].[Year]) > 0 ) } ,
5.000000, [SalesCube].([actvsbud].[Actual],[region].[Canada],[account1].[Price],[month].[Year]))}, [SalesCube].([actvsbud].[Actual],[region].[Canada],[account1].[Price],[month].[Year]), BDESC) }

Probably not as simple as you were hoping but at least it is a way.

Regards

Paul Simon

[abcuser]

Hi,
bellow is MDX query from Report Studio that I can manually change. Can someone help me change the bellow query. Most probably there should be some FILTER command inside bold text bellow. Any idea how to add this filter to bellow MDX?
Regards

Dictionary Slovenian word / English word:
IZDELEK = PRODUCT
ÄŒAS = TIME
POG MARŽA = SALES MARGIN
MERA = ACCOUNT DIMENSION
Regards

MDX query:
SELECT UNION(UNION({[PROD_MARZE_CAS_POGODBE].[PROD_MARZE_CAS_POGODBE].[@MEMBER].[ÄŒAS POGODBE].LASTCHILD.LAG(2)}, {[PROD_MARZE_CAS_POGODBE].[PROD_MARZE_CAS_POGODBE].[@MEMBER].[ÄŒAS POGODBE].LASTCHILD.LAG(1)}), {[PROD_MARZE_CAS_POGODBE].[PROD_MARZE_CAS_POGODBE].[@MEMBER].[ÄŒAS POGODBE].LASTCHILD}) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0),
BOTTOMCOUNT
(
DESCENDANTS
(
[PROD_MARZE_IZDELEK].[PROD_MARZE_IZDELEK].[@MEMBER].[IZDELEK], 1
),
5,
([PROD_MARZE_CAS_POGODBE].[PROD_MARZE_CAS_POGODBE].[@MEMBER].[ÄŒAS POGODBE].LASTCHILD,
[PROD_MARZE_MERA].[PROD_MARZE_MERA].[@MEMBER].[POG MARŽA]
)
)
DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1),
{[PROD_MARZE_MERA].[PROD_MARZE_MERA].[@MEMBER].[POG MARŽA]}
DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2)
FROM [POG_MARZE]

[cognostm1guy]

Keep it simple.

Use backslash \ instead of forward slash / when using in division calculation.

It will return zero when the denominator has zero value.