Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

edward_kirk
New Contributor

Number recognised as money rather than normal number

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!

1 Solution

Accepted Solutions
tomasz_tru
Valued Contributor

Re: Number recognised as money rather than normal number

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;

8 Replies
tomasz_tru
Valued Contributor

Re: Number recognised as money rather than normal number

Can you give us sample app?

edward_kirk
New Contributor

Re: Number recognised as money rather than normal number

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
Valued Contributor

Re: Number recognised as money rather than normal number

Try:

LOAD num#("$Total Incurred"),

    MatchName;

martinpohl
Valued Contributor II

Re: Number recognised as money rather than normal number

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

edward_kirk
New Contributor

Re: Number recognised as money rather than normal number

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?

mov
Esteemed Contributor III

Re: Number recognised as money rather than normal number

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
Valued Contributor

Re: Number recognised as money rather than normal number

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;

edward_kirk
New Contributor

Re: Number recognised as money rather than normal number

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