14 Replies Latest reply: Jan 25, 2018 12:13 AM by pranav gohil

# can we change sorting rule based on expression?

hello community,

i have this doubt in my app

i created a bar chart in which i used below expression as measure:

##### Num(Sum(Persons)/100000, '###.##Lacs'))

this expression checks the value and if its greater than 1cr is appends cr after the value else it appends lacs.

after using this i'm getting following output:

#### so the problem here is it shows 35.86lacs value bar larger than 4.39cr so is there any way i can impose some rule where it will treat lacs value smaller than crore value..

• ###### Re: can we change sorting rule based on expression?

Hi Pranav,

I have followed marcowedel post to achieve it.

kindly find the attached sample app.

## 'K','Mil','Bil','Tril') are available in that application.

I think you can change to Lac & Cr

##### )

use this expression and may be if required use measure expression in the number formatting instead of auto.

• ###### Re: can we change sorting rule based on expression?

this way it shows value as 0.36 (i.e) in crores only and it doesnt even append cr at the end

• ###### Re: can we change sorting rule based on expression?

hi Pranav

that's why asked you to use measure expression in the formatting.

• ###### Re: can we change sorting rule based on expression?

see this its not happening

• ###### Re: can we change sorting rule based on expression?

##### )
• ###### Re: can we change sorting rule based on expression?

I have tried with dual and its working in my app.

• ###### Re: can we change sorting rule based on expression?

Hi,

maybe one solution could be:

using this expression:

```If(Sum(Persons)>10000000,
Dual(Money(Sum(Persons)/10000000, '###.##Cr',',','.'),Sum(Persons)),
Dual(Money(Sum(Persons)/100000, '###.##Lacs',',','.'),Sum(Persons))
)
```

hope this helps

regards

Marco

• ###### Re: can we change sorting rule based on expression?

or sorted by measure expression value:

hope this helps

regards

Marco

• ###### Re: can we change sorting rule based on expression?

hello sir, marcowedel

I came up with one problem with this expression,

If(Sum([Sales Amount])>10000000,

Dual(Money(Sum([Sales Amount])/10000000, '###.##Cr','.'),Sum([Sales Amount])),

If(Sum([Sales Amount])<100000000 and Sum([Sales Amount])>100000,

Dual(Money(Sum([Sales Amount])/100000, '###.##Lacs','.'),Sum([Sales Amount])),

If(Sum([Sales Amount])<100000 and Sum([Sales Amount])>0,

Dual(Money(Sum([Sales Amount])/1000, '###.##K','.'),Sum([Sales Amount]))

,If(Sum([Sales Amount])=0,0.00)

)

)

)

in this, it shows value in lacs crore and thousands but what if the value is negative ?? it fails to display those values in the format defined by expression, please help me with this sir.

Example:

• ###### Re: can we change sorting rule based on expression?

Hi,

you could check e.g. for fabs(Sum(Persons))>100000

Fabs - script and chart function ‒ QlikView

regards

Marco

• ###### Re: can we change sorting rule based on expression?

Yes this works but what if I want to show value in  negative  if it is negative,

like if my table has values like

example: some positive and some negative values

 FY Year Name cr& lac Final correct 2010-11 0.00 2011-12 -12.71Cr  (negative) 2012-13 51.03Cr 2013-14 62.95Cr 2014-15 64.15Cr 2015-16 114.84Cr 2016-17 -76.88Cr  (negative) 2017-18 50.69Cr - 24.12K

regards

Pranav