How can i avoid If in my expression in case i need to make the eXP chart show in +ve ?

[paulyeo11]

Hi All

This is my org expression , the issue is when user click on eXP , the value will display in -ve Bar , which look funny :-

money(

(

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000

)

, $(vMoneyFormatK))

After change the above expression to below , i manage to make eXP display on +ve .

if

(

match([Pls select 66 Dim],'rEVENUE'),

money(

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000,

$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'sales'),

money(

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000,

$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'eXP'),

money(

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)*-1/1000,

$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'n_PRO'),

money(

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000,

$(vMoneyFormatK)),

))))

My question is , it there more simple solution ?

Can i avoid  using the if ?

One more question is for long expression like above , it will affect the performance , i mean create slow display of result.

I will post my QV example in next posting.

Paul

[paulyeo11]

Hi All

Enclosed my QV Doc

[jagan]

HI,

Try Fab(Your Expression) to convert negative values to positive.

Regards,

Jagan.

[paulyeo11]

Hi Jagan

Your suggestion sound good , because it work fine for eXP.

But net profit field i have +ve and -ve value. So it cannot work.

So only solution is using if command.

Paul

[jagan]

Yes, it will convert -ve to +ve all numbers, if you want conditionally then you have use if

=If(Dim = 'Some Value', Expression, Fabs(Expression))

HOpe this helps you.

Regards,

Jagan.

[avinashelite]

since most of the expression looks same you can try like this

if

(

match([Pls select 66 Dim],'rEVENUE','sales','n_PRO' ),

money(

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000,

$(vMoneyFormatK)),

if

(

match([Pls select 66 Dim],'eXP'),

money(

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)*-1/1000,

$(vMoneyFormatK)),

))

[Kushal_Chawda · Accepted solution]

try with Pick() & match() to optimize the IF

money(pick(

match([Pls select 66 Dim],'rEVENUE','sales','eXP','n_PRO'),

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000,

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000,

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)*-1/1000,

Sum({<$(ColumnDim51)={$(ColumnDim51)}>}Amount)/$(Columndim89)/1000 ),

$(vMoneyFormatK))

[paulyeo11]

Hi All

Thank you very much for provide me with a different approach.

I prefer Pick and match , it is more neat.

Paul