Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:

if(Sum(Persons)>10000000, Num(Sum(Persons)/10000000, '###.##Cr'),
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:

sortttttttttttt.PNG

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

please help..

14 Replies
MarcoWedel

Hi,

maybe one solution could be:

QlikCommunity_Thread_286488_Pic1.JPG

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

MarcoWedel

or sorted by measure expression value:

QlikCommunity_Thread_286488_Pic2.JPG

hope this helps

regards

Marco

Anonymous
Not applicable
Author

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:

CRLAC1.PNGCRLAC2.PNG

MarcoWedel

Hi,

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

see also:

Fabs - script and chart function ‒ QlikView

regards

Marco

Anonymous
Not applicable
Author

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 Namecr& lac Final correct
2010-110.00
2011-12-12.71Cr  (negative)
2012-1351.03Cr
2013-1462.95Cr
2014-1564.15Cr
2015-16114.84Cr
2016-17-76.88Cr  (negative)
2017-1850.69Cr
-24.12K

regards

Pranav