Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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.
for the row with 1500 in both fields value, you'll get 1500 as result not 3000 applying the above expression.
that what I expected to see. i.e. calculated value to be 1500 but I get 3000?
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!
if(ChgOut<>0 or ChgPost<>0,RangeMax(sum(ChgOut),sum(ChgPost)), RangeMin(sum(ChgOut),sum(ChgPost)))
result: