cancel
Showing results for 
Search instead for 
Did you mean: 

How to split amounts in Key Figure in 2 different columns based on cond

Former Member
0 Kudos

Hi,

I am trying to accomplish the following.

I have a key figure called amount in transaction currency. The values are get are positive and negative amounts.

The requirement is that I have 2 separate columns in the query/report where one column would have only positive values or 0, and another one all the negative values.

basically they want to see disbursements and credits in 2 separate columns.

Anybody knows how to accomplish that with BEx Query Designer.

P.S. I am running BI 7.0 and using BEx WEB analyzer to display queries.

Thank you

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi AG,

This is so simple, just create two formula and use condition in that.

Ex.

if your query shows output.

Characteristic Value

just create another two formula

characterisctic value formula1 formula2.

where formula1 = (Value > 0) * Value

and formula2 = (Value < 0) * Value.

then hide value.

now what is condition, when you use operator in formula, if condition satisfied then it will give 1 or it will give 0.

here if Value is gt 0 then (Value>0) = 1. so formula 1 = value.

and (Value<0) = 0. so formula2 - 0.

vice versa.

regards,

Answers (2)

Answers (2)

Former Member
0 Kudos

You can create 2 formula with boolean operators in BEX structure

Formula1:

if [keyfigure >= 0] then display keyfigure, else '0'

Formula 2:

if [keyfigure < 0] then display keyfigure, else '0'

Former Member
0 Kudos

Keep your KF in Column and Create two formula in Column area.

KF1

(KF > 0) x 1 + (KF < 0) *0

KF2

(KF < 0) x 1 + (KF > 0) *0

In Query properties value display tab select Zero as space.

I hope this will solve your problem

Regards,

kams