topic Re: Format as a Number in QlikView

Format as a Number

evansabres — Fri, 29 Sep 2017 20:23:56 GMT

I have an expression

=NUM(SUM(transTOTALSALE),'#$,###.##')+ NUM(SUM({$<transTYPE={"RETURN"},}>}transQUANTITY * transBASEPRICE),'$#,###.##') - projREV

I want this result to be shown as a dollar amount. However, the result is just displaying as 33980 as opposed to $33,980.

Do I have the structure of the expression incorrect?

Re: Format as a Number

krishnacbe — Fri, 29 Sep 2017 20:28:07 GMT

Try below expression.

=NUM((SUM(transTOTALSALE)+ SUM({$<transTYPE={"RETURN"},}>}transQUANTITY * transBASEPRICE ) - projREV,,'#$,###.##')

Re: Format as a Number

evansabres — Fri, 29 Sep 2017 20:29:50 GMT

When I enter this into the expression dialogue box, I get a message of 'Error in Expression'

Re: Format as a Number

krishnacbe — Fri, 29 Sep 2017 20:31:58 GMT

Sorry there was a Typo. Try below

=NUM((SUM(transTOTALSALE)+ SUM({$<transTYPE={"RETURN"}>}transQUANTITY * transBASEPRICE ) - projREV,'#$,###.##')

Re: Format as a Number

its_anandrjs — Fri, 29 Sep 2017 20:32:03 GMT

Try to add this

=NUM(SUM(transTOTALSALE),'USD #,##0.00', '.',',')+ NUM(SUM({$<transTYPE={"RETURN"} }>} transQUANTITY * transBASEPRICE),'USD #,##0.00', '.',',') - projREV

Re: Format as a Number

krishnacbe — Fri, 29 Sep 2017 20:33:12 GMT

=NUM((SUM(transTOTALSALE)+ SUM({$<transTYPE={"RETURN"}>}transQUANTITY * transBASEPRICE )) - projREV,'#$,###.##')

Re: Format as a Number

evansabres — Fri, 29 Sep 2017 20:34:55 GMT

Thank you

Re: Format as a Number

krishnacbe — Fri, 29 Sep 2017 20:35:08 GMT

=NUM((SUM(transTOTALSALE)+ SUM({$<transTYPE={"RETURN"}>}transQUANTITY * transBASEPRICE )) - projREV,'$#,##0.00')

Re: Format as a Number

jduenyas — Fri, 29 Sep 2017 23:14:29 GMT

Why do you have the extra # in front of the $ sign. That messes up the formatting.

NUM(SUM(transTOTALSALE),'#$,###.##')+...

Having said that, the other reply is correct . NUM( expression + expression +... 'FORMAT')

You may also NUM(Expression,'FORMAT') + NUM(Expression,'FORMAT') + ... but it is not necessary and takes longer to process.