topic Re: Comma Separator in Expression in QlikView

Comma Separator in Expression

noman212 — Fri, 31 Jul 2015 06:54:23 GMT

Hi Community,

I want to put comma seperator in a text object this expression returns values like 2500000000.

when i put ,'#,##0'

FirstSortedValue(AGGR(if(ModeType='A',Round(sum({$<statuscode={'13'}>} Annual,'#,##0')),

But it returns null value.

Please Help.

Original Expression :

=FirstSortedValue(AGGR(if(ModeType='A',Round(sum({$<statuscode={'13'}>} Annual)),

if(ModeType='H',Round(sum({$<statuscode={'13'}>} Half)),

if(ModeType='Q',Round(sum({$<statuscode={'13'}>} Quar)),

Round(sum({$<statuscode={'13'}>} Monthly))))),Clientname),

-AGGR(if(ModeType='A',Round(sum({$<statuscode={'13'}>} Annual)),

if(ModeType='H',Round(sum({$<statuscode={'13'}>} Half)),

if(ModeType='Q',Round(sum({$<statuscode={'13'}>} Quar)),

Round(sum({$<statuscode={'13'}>} Monthly)))))+rank(Clientname,Clientname)/10,Clientname),2) & CHR(10) &

Regards

Noman khan

Re: Comma Separator in Expression

maxgro — Fri, 31 Jul 2015 07:03:54 GMT

maybe I don't understand but if you only want to format the expression expr

num(expr, 'format')

in your case num(expr, '#,##0')

Re: Comma Separator in Expression

swuehl — Fri, 31 Jul 2015 07:06:40 GMT

You can only use the format code in formatting (or interpretation) functions, like num(), not in aggregation functions like Firstsortedvalue().Embed your aggregation in a num() function. You may also need to specify your delimiters in the num() function, depending on your default settings.

Re: Comma Separator in Expression

sasiparupudi1 — Fri, 31 Jul 2015 08:43:02 GMT

Try

=num(FirstSortedValue(AGGR(if(ModeType='A',Round(sum({$<statuscode={'13'}>} Annual)),

if(ModeType='H',Round(sum({$<statuscode={'13'}>} Half)),

if(ModeType='Q',Round(sum({$<statuscode={'13'}>} Quar)),

Round(sum({$<statuscode={'13'}>} Monthly))))),Clientname),

-AGGR(if(ModeType='A',Round(sum({$<statuscode={'13'}>} Annual)),

if(ModeType='H',Round(sum({$<statuscode={'13'}>} Half)),

if(ModeType='Q',Round(sum({$<statuscode={'13'}>} Quar)),

Round(sum({$<statuscode={'13'}>} Monthly)))))+rank(Clientname,Clientname)/10,Clientname),2) & CHR(10) &,'#,##0')