Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
I have a table , with Country as a Dimension, and two metrics : Value2 and Value3.
My table looks like this :
I'd like to put the Value3 for GBR in all the other Countries. Is there any way to do it by set Analysis?
Many thanks in advance.
Regards, Marcel.
Hi Marcel
If I have understood you correctly I think you want to place the value of 5093.211 in all the rows in Value 3 e.g.
Country | Value2 | Value3 | Desired Value 3 |
GBR | 478.658 | 5093.211 | 5093.211 |
DEU | 5921.855 | 680 | 5093.211 |
ESP | 4227.89 | 68 | 5093.211 |
FRA | 2059.342 | 119 | 5093.211 |
One way to do this is to use the field value and field index functions.
FieldIndex will detect the row in which the GBR value exists. Field value will call the value of Value 3 when Country is GBR.
The expression to do this is:
=Sum(FieldValue('Value3',FieldIndex('Country','GBR')))
This will generate a value in each row which you could use to e.g. compare each country's figure with that of GBR.
e.g.
=Sum(Value3)-Sum(FieldValue('Value3',FieldIndex('Country','GBR')))
Hope that helps.
Kind regards
Steve
Yes, that's what I want to do. I'm trying to do it like you do. But I can't.
Coud you attach a dummy example?
Many thanks in advance Steven.
GBR-Value3
=Sum(FieldValue('Value3',FieldIndex('Country','GBR')))-Sum(Value3)
=Sum(FieldValue('Value3',FieldIndex('Country','GBR')))-Sum(Value2)
num(
Sum(Value2)
/Sum(FieldValue('Value3',FieldIndex('Country','GBR')))
,'0.00%')
Country | Value2 | Value3 | GBR Value3 | GBR-Value3 | GBR-Value2 | % of GBR |
GBR | 478.658 | 5093.211 | 5093.211 | 0 | 4614.553 | 9.40% |
DEU | 5921.855 | 680 | 5093.211 | 4413.211 | -828.644 | 116.27% |
ESP | 4227.89 | 68 | 5093.211 | 5025.211 | 865.321 | 83.01% |
FRA | 2059.342 | 119 | 5093.211 | 4974.211 | 3033.869 | 40.43% |
Wow! That's amazing Steven,
Now I see why it doesn't work for me.
My problem is that I have a complex model.
I have Dimension1, ...., DimensionN and Metric1,...,MetricN.
and my sum(Value3) looks like sum( {$< Value1={'1'}, Value2 ={'2'} >} Value3).
Maybe the clue is to get an unique key with Dimension1&'-'&Dimension2&'-'&.....&DimensionN as link.
and get as well an unique value per each unique dimension.
As a general approach, I would use:
=sum({<Country={GBR}>}TOTAL Value3)
TOTAL escapes the dimension you are in, Combining that with what you posted above it would be:
sum( {$< Value1={'1'}, Value2 ={'2'}, Country={GBR} >} TOTAL Value3).
-Rob
in Value3 write this expression--
if(Country<>'GBR','5.093.211E',Value3)
Nice approach Rob,
now it looks like this :
I need to be divided by "FieldTram" as well, and it must be selected by year, month and another temporal values.
Many thanks for your help Rob. I almost got it.
Regards, Marcel.