Query definition - exclusion of calculated formulas

[Former Member]

Hello

I have a formula 1 and a formula 2.

Let's say formula 1 has the result 1 = 100

On the other hand formula 2 has the result 2 = 30.

Another formula 3 subtracts the values of both formulas, 1 and 2, result 3 = 70.

Another example:

Formula 1, result 1 = 100

Formula 2, result 2 = ' '

As we can see, result 2 has no values as there are no records available in the InfoCube.

Now I would like to calculate formula 3 in the way, that this constellation of result 1 (=100) and result 2 (=' ') is ignored. That means there should be NO value be calculated for result 3.

How can this be achieved?

Regards

[Former Member · Accepted solution]

Hi there,

You can use a condition to execute the formula differentiate only when formula 2 is greater than zero, otherwise it will return zero:

Formula 3 = (formula 2 GT 0 ) * (formula 1 - formula 2)

Regards,

Diogo.

[Former Member · Accepted solution]

Hi Diogo

Does it mean that result 2 = ' ' from formula 2 cannot be excluded from a calculation in result 3?

[Former Member · Accepted solution]

Hi there,

It is beiing excluded, that's what the first part of the formula do:

Formula 3 = (formula 2 GT 0 ) * (formula 1 - formula 2)

i.e., (formula 2 GT 0) is a conditional that will return 1 as true and 0 as false, i.e., if formula 2 is greater than zero (formula 2 GT 0) it will return 1, so 1(formula 1 - formula 2) = formula 1 - formula 2, if formula 2 is zero (or blank ''), the condition will return false, i.e., will return 0, so the result will be 0(formula 1 - formula 2) = 0

Hope this helps,

Regards,

Diogo.

[Former Member]

Hi,

Good example,

Can you also explain the 3rd key figure which you want to display as blank. What is the relevance ot it.

Regards,

Shyam.

[Former Member]

you can use the key figure properties to change 0 as a blank.

that means by using above boolean logic it'll show 0 and this setting will make 0 as a blank.

Hope it helps.....

[Former Member]

All right, I would like to explain it from my view.

Two cubes are assigend to a multiprovider.

Cube 1 contains actual data.

Cube 2 contains plan data.

F1 is calculated on one characteristic, called customer and the keyfigure revenue, coming from cube 1 (actual data).

F2 is calculated on one characteristic, called customer and the keyfigure revenue, coming from cube 2 (plan data).

Customer 007 (James Bond) belongs to customer group MI6 (text: British Secret Service) and caused an actual revenue of 100 Moneypennies in May 2011.

The same customer 007 has no records in May 2011 in the planning area (cube 2), It means there were never uploaded planning data into this cube, therefore no records.

In the report (BEx Analyzer) I will definetely have two columns, one columne with actual data of 100 Moneypennies and one column which is blank (NOT zero) for the planning case. On the same line the customer 007 and month May 2011 is listed.

Any ideas?

Thanks

[Former Member]

Hi,

First of all, top example

Now, for your scenario, i think my fav BW function might help. Exception Aggregation.

On all there CKFs F1, F2, F3. Select "Exception Aggregation" as "Total" with Ref Char "Calmonth".

This will basically collect things for Month and then Calculate and aggregate the total.

[Former Member]

Thanks Parth

I think you mean "sum" as exemption aggregation? Do you agree.

Regards

[Former Member]

No. 'Total'. I doubt you have 'Sum' as option there in BI 7.0.

[Former Member]

Hi,

I agree with above posts, if there no value in Cube, system considers that as 0.

There must be some Characteristic on basis of which you can identify if there is record. Then something could be worked upon.

[Former Member]

Hi,

If F2 is a calculated fields it cannot be blank until there are the mappings from the cube to the multiprovider is missing for the underlying key figs. That is the only case when it can be blank, else it will be always 0 even for F2.

Regards,

Shyam.

[Former Member]

Hi,

Understanding from the question is that if any of F1 or F2 is ' ' you need F3 to be zero. So it can be achieved by the above method.

If this is not the case then let us know.

Regards,

Shyam.

[Former Member]

Dear Shyam

No, this is wrong.

If one of F1 or F2 is ' ' than F3 shouldn't be zero. F3 shouldn't be calculated or better it should also get ' '.

[Former Member]

Hi,

Try using boolean conditions.

eg F3 = (F1 != 0 )(F2 != 0) (F1 - F2)

In this case if any of F1 or F2 is zero , the condition will return 0 and hence F3 will be zero.

Regards,

Shyam.

[Former Member]

Thanks Shyam

But F2 is not zero, it has no value as there is no record in the comparing infocube.

Intention for such case is that we don't want to calculate F3...

Do you understand?

Regards