number format using set analysis

[#1]

hi

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.

[CELAMBARASAN]

Hi,

     Check with this

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

Celambarasan

[#3]

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

[#4]

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

[#5]

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

[#6]

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.

[swuehl]

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

[#8]

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

[#9]

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.

[CELAMBARASAN]

Hi,

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

Celambarasan