Fact Expression

[petperson]

Am trying to get this to work in a fact, but syntax is wrong someplace. The object of this is to add logic for the divide by 0 error - can all this be done in one fact? thanks

ApplySimple("([ORDER_COST_GROSS] / CASE WHEN #0 = 0 THEN NULL ELSE #0 END",[ORDER_QTY]) * [RECEIPTS_QTY])

 

[entaroadun]

Do you really need this in a fact? It affects the way the metric rolls up:

SUM([ORDER_COST_GROSS]/[ORDER_QTY])

is not equal to

SUM([ORDER_COST_GROSS]) / SUM([ORDER_QTY])

just as

1/2 + 1/3 <> 1/(2+3)


The expression is throwing an error because of a missing parenthesis after the END in the CASE statement.

 

[entaroadun]

Oh, BTW, there is a VLDB setting that does a NULL check whenever a division is performed. I think this is on by default.

 

[petperson]

thanks for the suggestions. Yes, I need this as a fact so that it will calculate it at the row level. I need it to calculate the unit cost a row level then and multiply it by the receipt qty to get a true receipt $. I did get the fact to work. The VLDB setting for a zero check does not work on this fact - I'm assuming because maybe it's a 'compound' fact? The setting works correctly for other facts.

 

[entaroadun]

The VLDB setting only applies to compound metrics. MSTR has no idea what you're doing in those lovely strings inside your Apply statements. If it doesn't know you're performing division, it cannot apply the setting.

 

[omarmtz]

ApplySimple(&quot;([ORDER_COST_GROSS] / CASE WHEN #0 = 0 THEN NULL ELSE #0 END&quot;,[ORDER_QTY]) * [RECEIPTS_QTY])

You have the parenthesis in wrong place, because you are doing the product of ORDER_QTY and RECEIPTS_QTY out of the expression, you need to put this like the following:

ApplySimple(&quot;(CASE WHEN #1 = 0 THEN NULL ELSE (#0 / #1)* #2 END&quot;, [ORDER_COST_GROSS], [ORDER_QTY], [RECEIPTS_QTY])