Num( Function not working

[#1]

Good Morning All,

I have been using the num function without issue in all my Qlik Charts but for some reason I cannot get it to work in the following:

'LY - ' & if (GetCurrentSelections(Year)=2016,

num(Sum({<_YTD_LY={1}>} [Sales total with VAT])

/ Count({<_YTD_LY={1}>} DISTINCT [Sales invoice]), '£#.##'),

num(Sum({<[Year]={"$(=max([Year]-1))"}>} [Sales total with VAT])

/ Count({<[Year]={"$(=max([Year]-1))"}>} DISTINCT [Sales invoice]), '£#.##'),

)

This does not format the number at all. As a result, instead of £15.42 I instead have 15.419583493 (for example). Also, if it helps, this is under the Subtitle field in Qlik Sense.

I appreciate any assistance you can give me with regards to this issue.

Thank you in advance.

Kind regards,

Michael

[Gysbert_Wassenaar · Accepted solution]

Try putting the num on the outside of the if statement.

[jpenuliar]

you seem to have an extra comma "," within your IF statement.

'LY - ' &

if (GetCurrentSelections(Year)=2016

    ,num(Sum({<_YTD_LY={1}>} [Sales total with VAT])/Count({<_YTD_LY={1}>} DISTINCT [Sales invoice]), '£#.##')

    ,num(Sum({<[Year]={"$(=max([Year]-1))"}>} [Sales total with VAT])/Count({<[Year]={"$(=max([Year]-1))"}>} DISTINCT [Sales invoice]), '£#.##')

    ,   <<<<<<<<<<<<<<< this one

    )

[ToniKautto]

Is pound £ your currency format in the application?

If so, I would suggest that you confirm the money variables in the load script are set according to your preference, for example as below.

SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##.00;£-#,##.00';

Being currency, you then us the Money() format function.

'LY - ' & if( GetCurrentSelections(Year)=2016,
Money(Sum({<_YTD_LY={1}>} [Sales total with VAT]) / Count({<_YTD_LY={1}>} DISTINCT [Sales invoice]) ),
Money(Sum({<[Year]={"$(=max([Year]-1))"}>} [Sales total with VAT])/Count({<[Year]={"$(=max([Year]-1))"}>} DISTINCT [Sales invoice])))

[#5]

Thank you, this worked. It seems so obvious now!

Kind regards,

Michael

[#6]

My apologies, this is part of a larger expression and was poorly edited.

Kind regards,

Michael

[#7]

It is indeed setup correctly in the load script, but thank you very much for the advice re:"Money()" - this will be useful for my future multi-currency analysis.

Kind regards,

Michael