Calculated dimension based on a value of one of the cells

[Anonymous]

Hello,

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

ChgPost	ChgOut	Calculated
1000	0	1000
1500	1500	1500
200	0	200
300	0	300
0	300	300
0	350	350
250	250	250
300	250	250

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

ChgPost	ChgOut	Calculated
1000	0	1000
1500	1500	3000
200	0	200
300	0	300
0	300	300
0	350	350
250	250	500
300	250	550

Could you please advice how to fix it?

Thanks,

Leszek

[Anonymous]

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

Thank you for your help as always.

Thanks,

Leszek

[agigliotti · Accepted solution]

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]

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]

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

[Anonymous]

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

[Anonymous]

[Anonymous]

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: