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

[Anonymous]

Hi,

I have this table:

Item	Currency	Price
A	USD	250,00
A	USD	250,00
B	EUR	180,00
B	EUR	180,00
C	USD	100,00
C	USD	100,00
D	EUR	80,00
D	EUR	80,00
D	EUR	80,00

In Qlik Sense I want it to look like this:

Item	Price
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:

Item	Minus or Plus	Price
A	Minus	10,50
B	Plus	12,60
C	Plus	18,00
D	Minus	25,95
E	Plus	60,05

In Qlik Sense I want it to look like this:

Item	Price
Totall	54,20
A	- 10,50
B	12,60
C	18,00
D	-25,95
E	60,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?

[robert_mika]

Your first question:

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)

[sll · Accepted solution]

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

Price:

LOAD [Item],

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

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

(ooxml, embedded labels, table is Sheet1);

Asim

[ogster1974]

Second Question

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

[robert_mika]

For the second question:

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

[Anonymous]

Thanks, it's working!

[Anonymous]

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

[robert_mika]

Works for me:

[Anonymous]

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

Thanks!

[Anonymous]

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...