Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

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

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

1 Solution

Accepted Solutions

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

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))

7 Replies
paulyeo11
Valued Contributor II

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

Hi All

Enclosed my QV Doc

MVP
MVP

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

HI,

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

Regards,

Jagan.

paulyeo11
Valued Contributor II

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

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

MVP
MVP

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

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.

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

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)),

))

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

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
Valued Contributor II

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

Hi All

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

I prefer Pick and match , it is more neat.

Paul

Community Browser