Custom Number Formatting Sorting

[ShellyG]

Dear all,

I have the following expression:

if(
Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)) > 1000000,
num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000000, '#,###M'),
if(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)) > 1000,
num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000, '#,###K'),
num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)), '#,###')))

I have a table with dimensions and the corresponding numbers. All calculations are accurate and they have properly the 'K' symbol and 'M' symbol at the end for thousands and millions. 

The problem starts when I have to sort the data by the numbers. In my table I want to see the top 5 but instead they are being sorted by something which results in wrong sort - I see numbers with K at the end (thousands) above numbers with M at the end (for millions) which is not supposed to be like this. I based my sorting on the following calculation - Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)) but it doesn't work...

For instance:

Dimension 1       600.5K
Dimension 2       600
Dimension 3       500
Dimension 4       400
Dimension 5       40.5M

Any ideas on how to make Qlik understand that numbers which have M at the end are actually higher than numbers with K at the end? 

Thanks in advance!

Best Regards,

Shelly

[dplr-rn]

try making it a dual(actual number value, display value).

so for example dual ( Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))

, num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000000, '#,###M'))

[ShellyG]

Hello Dilipranjith,

Thank you very much for your fast reply! 

I have tried the below but it doesn't seem to work... Can you please take a look, as probably I am not using your suggestion properly:

if(
Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)) > 1000000,
dual ( Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))
,num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000000, '#,###M')),
if(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)) > 1000,
dual ( Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))
,num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000, '#,###K')),
num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)), '#,###')))

Thanks!

[dplr-rn]

looks like you have got your order wrong its text first then the value. you have it as value, text

see simple example i cooked up.

expression

dual(
	if(Sum(LineSalesAmount)>1000
		,Num(Sum(LineSalesAmount)/1000,'#,###K')	
		,Num(Sum(LineSalesAmount),'#,###.##')
	)
	,Sum(LineSalesAmount)
	)

Make sure format is measure expression

result

[ShellyG]

Hello Dilipranjith,

Thanks for your reply! 

I works properly now, but my total is getting distorted... It shows '1.4e+04K'...

I believe I should somehow modify the expression so that it has the millions also, but so far everything that I tried was not working at all...

Any ideas? 

Thanks very much!

[dplr-rn]

it must be something to do with formatting and your if statements.

share final one once more i can check and get back

[ShellyG]

Hello Dilipranjith,

For now I am only using the following and this is where I get the number formatting issue:

dual(
if(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))>1000
,Num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000,'#,###K')
,Num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)),'#,###.##')
)
,Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))
)

I used combinations similar to this as well, but then I get an only '-' displayed where my numbers should be:

dual(
if(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))>1000
,Num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000,'#,###K')
,Num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)),'#,###.##')
if(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))>1000000
,Num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))/1000000,'#,###M')
,Num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)),'#,###.##')
))
,Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))
)

[dplr-rn · Accepted solution]

try this

dual(
if(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))>1000,
   if(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))>1000000
    ,Num(Sum({<CalendarDate= 
    {'$(=max(CalendarDate))'}>}$(vField))/100000,'###,###M')
    ,Num(Sum({<CalendarDate= 
    {'$(=max(CalendarDate))'}>}$(vField))/1000,'###,###K')
    )

,Num(Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField)),'###,###.##')
)
,Sum({<CalendarDate={'$(=max(CalendarDate))'}>}$(vField))
)

[ShellyG]

Hello Dilipranjith,

Thank you very much! 

It works perfectly!

Best Regards, 

Shelly