Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

leszekpiekielni
New Contributor III

Calculated dimension based on a value of one of the cells

Hello,

I need to create calculated dimension in the table as below:

   

ChgPostChgOutCalculated
100001000
150015001500
2000200
3000300
0300300
0350350
250250250
300250250

so Calculated = ChgPost  but if ChgPost = 0 then Calcuated = ChgOut.

I used the below formula:

If(ChgPost=0,ChgOut,ChgPost)

but it looks like the calculated = ChgPost+ChgOut

   

ChgPostChgOutCalculated
100001000
150015003000
2000200
3000300
0300300
0350350
250250500
300250550

Could you please advice how to fix it?

Thanks,

Leszek

1 Solution

Accepted Solutions
agigliotti
Honored Contributor II

Re: Calculated dimension based on a value of one of the cells

you can use the below expression in your measure:

Sum( if( ChgPost = 0, ChgOut, ChgPost ) )

OR

create a new field within your script as below:

LOAD

...

if( ChgPost = 0, ChgOut, ChgPost ) as Calculated

and use Sum(Calculated) as your measure.

8 Replies
leszekpiekielni
New Contributor III

Re: Calculated dimension based on a value of one of the cells

NOt sure if I worded it correctly, I am trying to calculate a filed in the table in  Data Manager view. Please see below.

OTW.JPG

Thank you for your help as always.

Thanks,

Leszek

agigliotti
Honored Contributor II

Re: Calculated dimension based on a value of one of the cells

you can use the below expression in your measure:

Sum( if( ChgPost = 0, ChgOut, ChgPost ) )

OR

create a new field within your script as below:

LOAD

...

if( ChgPost = 0, ChgOut, ChgPost ) as Calculated

and use Sum(Calculated) as your measure.

leszekpiekielni
New Contributor III

Re: Calculated dimension based on a value of one of the cells

HI Andrea,

thank you, I used the expression Sum( if( ChgPost = 0, ChgOut, ChgPost ) ) but the result looks like Calculated = ChgOut + ChgPost


Which is not what I wanted.

agigliotti
Honored Contributor II

Re: Calculated dimension based on a value of one of the cells

for the row with 1500 in both fields value, you'll get 1500 as result not 3000 applying the above expression.

leszekpiekielni
New Contributor III

Re: Calculated dimension based on a value of one of the cells

that what I expected to see. i.e. calculated value to be 1500 but I get 3000?

leszekpiekielni
New Contributor III

Re: Calculated dimension based on a value of one of the cells

OTW.JPG

leszekpiekielni
New Contributor III

Re: Calculated dimension based on a value of one of the cells

I realised where the mistake is, I changed the expression to

if( Sum(ChgPost) = 0, Sum(ChgOut), Sum(ChgPost))

and it worked as I am looking at Sum of ChgPost not ChrgPost itself. Thank you for your help!

omarbensalem
Esteemed Contributor

Re: Calculated dimension based on a value of one of the cells

if(ChgOut<>0 or ChgPost<>0,RangeMax(sum(ChgOut),sum(ChgPost)), RangeMin(sum(ChgOut),sum(ChgPost)))

result:

Capture.PNG