13 Replies Latest reply: Oct 29, 2015 10:28 AM by Louise Grauss-Spoormaker

# Can I have 2 different number formats in 1 column in Qlik Sense

Hi,

I have this table:

ItemCurrencyPrice
AUSD250,00
AUSD250,00
BEUR180,00
BEUR180,00
CUSD100,00
CUSD100,00
DEUR80,00
DEUR80,00
DEUR80,00

In Qlik Sense I want it to look like this:

ItemPrice
A\$ 500,00
B€ 360,00
C\$ 200,00
D€ 240,00

Is that possible?

Now I have everything in \$ or everything in €.

I tried formula's like if( Currency = USD, Price in \$, Price in €) but nothing is working.

So I am wondering if it's possible at all.

Else I will use a Pivot Table and split the column in USD and EUR.

(I don't want to use a currency conversion table.)

I also have a table like this:

ItemMinus or PlusPrice
AMinus10,50
BPlus12,60
CPlus18,00
DMinus25,95
EPlus60,05

In Qlik Sense I want it to look like this:

ItemPrice
Totall54,20
A- 10,50
B12,60
C18,00
D-25,95
E60,05

So if the Item is a expense I want it to be calculated as a minus amount.

And if it is a income I want it to be calculated as a plus amount.

Is that possible?

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

if(Currency='USD','\$'&sum( Price),'€'&sum(Price))

Bear in mind that this become now a string not number!

EDIT:

if(Currency='USD',num(sum( Price),'\$#,##0'),num(sum( Price),'€#,##0'))

Feeling Qlikngry?

How To /Missing Manual(18 articles)

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Thanks for your reaction, unfortunately it's not working.

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Works for me:

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

It's working, I made it an dimension in stead of a measurement...

Thanks!

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Can I ask you another question?

If I have an extra column, is it also possible to include that in the formula?

As you can see the field Curr & Price is with your formula and that's the same as the field Price so that's working good.

I also like to have the field Purchased Value in \$ and €.

The field Purchased Value is already a formula:

Sum([Purchased Unit Qty]*Price)

So I made a new formula for the field Pur Val & Curr:

if(Currency='USD',num(Sum([Purchased Unit Qty]*Price), '\$#,##0'),num(Sum([Purchased Unit Qty]*Price), '€#,##0'))

But that is not working...

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Try like this:

f(Currency='USD',num(Sum([Purchased Unit Qty])*Price, '\$#,##0'),num(Sum([Purchased Unit Qty])*Price, '€#,##0'))

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Thanks, it is working!

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Here is a script I wrote quickly. Accomplishes the same thing as above, just differently:

Price:

replace((replace(([Currency]&' '&[Price]), 'USD','\$')), 'EUR','€')  as FinalPrice

FROM [lib://test/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Asim

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Second Question

if([Minus or Plus]='Plus',Price,0-Price)

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Thanks, it's working!

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

For the second question:

if([Minus or Plus]='Minus',-sum( Price),sum( Price))

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Thanks, this is also working

• ###### Re: Can I have 2 different number formats in 1 column in Qlik Sense

Thanks, it's working!