Changing the format of a value

[thakurj]

I need to change the format of a value in a bar chart from $-#,##0 to -$#,##0.

The value needs to be on the negative axis in a bar chart so I can not use the '-$'&Value workaround here.

Could someone please help me with this?

[therealdees]

Hi,

You could try using dual. The Dual() function will receive 2 values, the first is a text string that will be used as a display value while the other is the actual numeric value (or expression) that Qlik Sense will interpret as the measure.

E.g: Dual(Num(10, '-$#,##0'), 10) (Something like this. You'd have to tweak the number formatting or maybe try to use concatenations)

[thakurj]

Hi @therealdees 
Using dual is not solving the problem here. Using the dual formula that you have mentioned, the format that is visible is 10 instead of $#,##0M.

Could you please make sure that this is the correct solution?

[therealdees]

Can you share the expression you've used?

[thakurj]

Hi @therealdees ,
Please find the expression below.

=if(condition,Dual(Num(Measure1,'#,##0'),Measure1),Dual(Num(Measure2,'$#,##0'),Measure2))

[therealdees]

Hi!

The expression seems to be correct. Are you sure the condition is being fulfilled to display the Dual instead of Measure1 or Measure2?

Any chance your formatting is different from the one set in the Main sheet in the loader script?

You've said the result being displayed is "10", is that the right result without the formatting you want?

[thakurj]

Hi @therealdees 

The formatting doesnt seem to be different in the main sheet or in the loader script.

The result being displayed is not supposed to be 10. It is supposed to be $10 if the measure selected is measure1 and 10 if the measure selected is measure2. Currently, however, the measures are being displayed in the auto format.
Any idea why this could be happening?

[therealdees]

Where are you selecting the measure from? Are you really selecting something or do you mean that if the result of the "condition" is equal to Measure1 then display x otherwise if it's equal to Measure2 display y?

It could be something related to your if condition. I could evaluate it if you're able to share it.

Dual should work in your case. Try tweaking with your condition result to see if the condition result changes whenever you select one or another measure.

Basically the Dual will work like this:

Dual('display this string', 10)

The displayed result will be the text string, but the actual value is equal to 10, so if you had the same function with different strings and numbers, you could sort the text strings numerically although they don't have any number displayed. In other words, the actual value is equal to the second argument of the function.

Let's say Measure1 = 500 and Measure2 = 1000

If x = y,

//THEN

Dual(Num(Measure1,'#,##0'),Measure1), // this will display 500,00 and the actual value will be 500,00 (same for both string and value)

//ELSE

Dual(Num(Measure2,'$#,##0'),Measure2) // this will display $1000,00 and the actual value will be 1000,00

You can try to tweak with the formatting as well:

If your decimal separator is set to "." in the Main section in the loader script, then try using:

'###0.00' (for numbers with decimal cases, e.g 10.00)

'##0' (for numbers without decimal cases, e.g 10)