topic number format using set analysis in QlikView

number format using set analysis

Thu, 05 Apr 2012 12:00:24 GMT

i had 5 different rows created by if statement for the data coming from same column distributorid in pivot table and for each row i used the expression sum(salary).

the salary starts with a $ sign.

i needed to remove the dollar sign from the ist row so i created a variable v and used sum(salary) expression for it.

now i used the expression in my pivot table as

if(distributorid='row1',num($(v),'###,00),sum(salary)

but its not converting my salary into required format.

number format using set analysis

CELAMBARASAN — Thu, 05 Apr 2012 12:09:09 GMT

Hi,

Check with this

if(RowNo()=1,num(Sum(Salary),'###,00),sum(salary))

Celambarasan

number format using set analysis

Tue, 10 Apr 2012 05:57:48 GMT

its not working...is there anyother funvtion to format the number according to our requirement???

number format using set analysis

Tue, 10 Apr 2012 08:36:10 GMT

Not really sure I understand but I think what you are going for is:

= NUM(IF(distributorid = 'row1', $(v), sum(salary)), '###,00')

Why I don't really understand is that if the values for salary are still numeric for distributorid = 'row1', then you should simply be able to just do:

= NUM(sum(salary), '###,00')

number format using set analysis

Tue, 10 Apr 2012 08:52:26 GMT

the problm is that i had to remove the $ sign only for ist row...for example i had 5 rows...for the ist row i only had to remove the $sign,.

thats why i have created a variable to evaluate the salary..and then using it in expression.

so thats why i willl have to use if condition in expression

number format using set analysis

Tue, 10 Apr 2012 09:01:29 GMT

I think you would be better off just stripping the $ off the salary field in your load script so you can use the salary field anywhere in any format without having to worry about it. You can use something like this to dump the unwanted characters:

purgechar(salary, '$') as salary

Wrap with num() (around the purgechar) if QV is giving you a string for that.

number format using set analysis

swuehl — Tue, 10 Apr 2012 09:40:46 GMT

I assume your salary field is a numeric field, so you could aggregate with sum(salary), not a text field.

I also assume that you have formatted the expression in your table to Money format (check the number tab in the table properties).

If you want to set the format using an expression, I think you need to set the number format to 'expression default' and then state all variants in the expression, like

if(rowno()=1,num(sum(salary),'###,00'),num(sum(salary),'$ ###,00'))

number format using set analysis

Tue, 10 Apr 2012 09:46:31 GMT

my situation can be better understood from the snap i have attached..

number format using set analysis

Tue, 10 Apr 2012 10:20:00 GMT

That image shows that a number of rows in the salary field have inconsistent formatting (ie. have been read in with '$' prefixed) to the other values in that field. So you just need to remove the '$' character from any rows in that field that contain it in your load script, so all values in that field are of the same format. Then you can format it with the number tab in chart properties or with the Money() function or the Num() function as you see fit.

number format using set analysis

CELAMBARASAN — Tue, 10 Apr 2012 10:41:09 GMT

Hi,

RowNo() is a function it should have paranthesis.You have missed that in your expression.

Celambarasan