topic Re: Num( Function not working in App Development

Num( Function not working

Tue, 30 Aug 2016 10:32:11 GMT

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

Re: Num( Function not working

Gysbert_Wassenaar — Tue, 30 Aug 2016 11:08:42 GMT

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

Re: Num( Function not working

jpenuliar — Tue, 30 Aug 2016 11:10:24 GMT

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

)

Re: Num( Function not working

ToniKautto — Tue, 30 Aug 2016 11:11:38 GMT

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])))

Re: Num( Function not working

Tue, 30 Aug 2016 12:29:16 GMT

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

Kind regards,

Michael

Re: Num( Function not working

Tue, 30 Aug 2016 12:30:02 GMT

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

Kind regards,

Michael

Re: Num( Function not working

Tue, 30 Aug 2016 12:30:53 GMT

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