Format the number part of dual()

[17George]

Hello,
I'm using dual(value, fabs(value)) as expression in QlikView to display the average value and keep the negative numbers visible but I would like, if possible,  to format the results with 1 decimal place instead of 12. 

(comma as decimal separator)

Is it somehow possible?
Regards

[edwin]

you should be able to set the number format in your table:

[17George]

Any number format modification, transforms the negative numbers to their absolute value but it's important to keep the negative values visible.

[edwin]

formatting always respects the sign:

[marcus_sommer]

The numeric part of a dual() couldn't be formatted because it's the real value behind everything but the displayed string-value could formatted, for example like:

dual(num(value, 'format'), fabs(value))

- Marcus

[17George]

Already tried dual(num(value, '#.##0,0'), fabs(value)) with no success.
It has no effect on the average result but only on the single values 

[edwin]

i see what you are doing.  you mad ethe value ABSOLUTE.  this means the actual sum will be positive no matter how you format it. 

converting the value to ABSOLUTE means you lose information - whether the value is actually + or -
i wouldnt use dual, and just format the measure in the table.

maybe there is another approach if you explain what you are trying to achieve.  what your output should look like.

[marcus_sommer]

Then maybe in this way:

dual(num(avg(value), 'format'), avg(fabs(value)))

By applying it in a pivot you may also consider to query the dimensionality() to use different expressions for the different levels.

- Marcus

[17George]

Same results as above adding avg() but in that case, if i choose "Expression Total" instead of  "Average" under "Total Mode" the results look like that:

6 or -6 as result would be the perfect solution

[marcus_sommer]

This means it's mainly not a formatting issue else the values didn't return your expected results. This is quite probably caused from the dimensionally context of your object which is different to the context in which the calculation needs to be done and which not fit to any of the available expression-modes.

In such a case you need to calculate the results with the right dimensionality which could be reached with an aggr(), for example:

avg(aggr(avg(Value), Dim1, Dim2))

whereby Dim1 + Dim2 are just placeholder for your needed dimensions. For the sake of simplicity is the dual() and formatting stuff not included in this example - and it may not easy to include.

- Marcus