Number recognised as money rather than normal number

[Anonymous]

Hi,

I'm using a combi chart to show two measures against each other on the same axis, one as bars and the other as markers. Qlik Sense seems to recognise the bars measure as a normal number, so when Number Formatting is set to "Auto" twenty million is displayed as 20M. However, the second measure is, I think, recognised by Qlik Sense as money which means that the axis displays 20000000, which looks messy and takes up too much space.

How do I get Qlik to treat the second field the same way as the first?

They both come from a SQL database. One thought that I had was that the second field has a $ sign in the column title, so perhaps that triggers something, but i would rather not have to get that changed.

Thanks!

[tomasz_tru]

Can you give us sample app?

[Anonymous]

Sure - see attached. I've had to strip pretty much everything out of it but I think that I have left in the relevant parts. Note that the two KPI boxes are both set to Auto number format - one shows "52.85M" while the other shows "$30,502,500".

Thank you!

[tomasz_tru]

Try:

LOAD num#("$Total Incurred"),
    MatchName;

[martinpohl]

Your fields are formatted different.

D_RNP is a number field

$total income is load with $ in the field.

So Qlik can't autodefine the format.

Load your datas without $ and format the value where neccesary with $.

But Auto is Auto and $ is $, so you won't get $20M

Regards

[Anonymous]

Thanks for the answers both. I tried the 'num#()' function but that means that the measure is now zero in all of the different charts (suspect that the numbers aren't recognised at all).

I would rather not change the columns in the database as I don't administer it and various other reports run off it, but looks as though that may be the answer.

Are there any other ways that it might be fixed in the app though before changes are made in SQL?

[Anonymous]

Try num() instead of num#()

Edit:  Actually you don't need to do it in the script.  I tried just to modify your "Measure 2", and it helped:

Sum( {<YOA -= {'9999'}>} num([$Total Incurred]))

[tomasz_tru · Accepted solution]

Works for me. Bet it's field name issue. If you haven't add alias for the field, please do so. 

LOAD num#("$Total Incurred") AS "$Total Incurred", 

MatchName;

[Anonymous]

works perfectly - thank you! I hadn't used that function before so didn't realise thatyou need to add an alias.