Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Partner - Champion
Partner - Champion

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.

View solution in original post

8 Replies
Anonymous
Not applicable
Author

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
Partner - Champion
Partner - Champion

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.

Anonymous
Not applicable
Author

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
Partner - Champion
Partner - Champion

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

OTW.JPG

Anonymous
Not applicable
Author

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

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

result:

Capture.PNG