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: 
sunil-kumar5
Creator II
Creator II

Change number value

Hi All,

I have a bar chart showing value like 1.65M,12.34M,342.45K,150.90K however the requirement is to show the value as

1M,12M,342K,150K

All the number after point should be removed and K and M should come with exact value.

Please advise

Thanks

21 Replies
chaorenzhu
Creator II
Creator II

Possible to share some sample data? just now I tried on some random data using your expression and it's working

sunil-kumar5
Creator II
Creator II
Author

Well, Sample data is not possible however if it is working for you fine may there is something on my end at somewhere, Is there any idea if I need to check on?

chaorenzhu
Creator II
Creator II

usually I break down piece by piece to check, does the following expression generate any value?

SUM({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote- Potential, INR/T (excluding transportation cost)]*[Quote-Volume (in Tons)])/
sum({<[Quote Active Status] -= {'InActive'},Status -={'Rejected'}>}
[Quote-Volume (in Tons)])

 

sunil-kumar5
Creator II
Creator II
Author

Yes, it is giving the perfect result

chaorenzhu
Creator II
Creator II

ok, now create a master measure using the above, then create a KPI using the following, how's the result?

if([your_measure]/1000000>=1,floor([your_measure]/1000000) & 'M',floor([your_measure]/1000)&'K')

sunil-kumar5
Creator II
Creator II
Author

The KPI giving me an unrealstic amount that is 120K.

Secondly I have noticed one thing that Bar chart does not reflect the value after putting the expression you suggest but when I hover on dimension It is showing the exact result that I want and which you suggest.

 

chaorenzhu
Creator II
Creator II

I can't tell how unrealistic it is since I don't know about your data, but I know where the issue came from for the bar chart.

If you need to draw bar you need to use dual()

dual(if([your_measure]/1000000>=1,floor([your_measure]/1000000) & 'M',floor([your_measure]/1000)&'K'),[your_measure])

Can you try this one?

Number formatting choose measure expression

sunil-kumar5
Creator II
Creator II
Author

Its working absolutly fine, thank you so much.

One last thing to add please advise how I would get the value of below 1000 as they are not appearing because we didn't mention it. I have tried from my end but all mixed up.

 

Thanks

chaorenzhu
Creator II
Creator II

Glad it works. How do you want the <1000 value to be displayed? If you just need the value without any K, use this 

dual(if([your_measure]/1000000>=1,floor([your_measure]/1000000) & 'M',if([your_measure]/1000>=1,floor([your_measure]/1000)&'K',[your_measure])),[your_measure])

sunil-kumar5
Creator II
Creator II
Author

Everything works fine, but the zero values does not come in the dimension hence only few records are showing. Without this expression I am getting all the records available in dimension.