topic Re: Set Analysis Challenge in Water Cooler

Set Analysis Challenge

marcel_olmo — Mon, 22 Jul 2013 21:29:30 GMT

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.

Re: Set Analysis Challenge

Mon, 22 Jul 2013 23:05:43 GMT

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

Re: Set Analysis Challenge

marcel_olmo — Mon, 22 Jul 2013 23:39:08 GMT

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.

Re: Set Analysis Challenge

Tue, 23 Jul 2013 01:38:58 GMT

Hi Marcel

No problem. I've attached a working example.

The example uses an inline load to replicate the first few lines of your data

LOAD * INLINE [

Country, Value2, Value3

GBR,478.658,5093.211

DEU,5921.855,680

ESP,4227.890,68

FRA,2059.342,119

];

I then created a pivot chart using the Country as the Dimension. The three expressions I used to create the Value2 and Value3 and the GBR Value3 values were as follows:

Value 2

=Sum(Value2)

Value3

=Sum(Value3)

GBR Value3

=Sum(FieldValue('Value3',FieldIndex('Country','GBR')))

I then added another three expressions to show the comparison between the country represented by the row and the GBR value.

GBR-Value3

=Sum(FieldValue('Value3',FieldIndex('Country','GBR')))-Sum(Value3)

GBR-Value2

=Sum(FieldValue('Value3',FieldIndex('Country','GBR')))-Sum(Value2)

% of GBR

num(

Sum(Value2)

/Sum(FieldValue('Value3',FieldIndex('Country','GBR')))

,'0.00%')

The final pivot table is:

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%

The single quotes in the expression for GBR Value3 are important as the functions won't work properly without them.

I hope that explains my solution and helps to resolve your question.

Kind regards

Steve

Re: Set Analysis Challenge

marcel_olmo — Tue, 23 Jul 2013 02:20:18 GMT

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.

Re: Set Analysis Challenge

rwunderlich — Tue, 23 Jul 2013 05:23:24 GMT

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

Re: Set Analysis Challenge

Tue, 23 Jul 2013 05:42:00 GMT

in Value3 write this expression--

if(Country<>'GBR','5.093.211E',Value3)

Re: Set Analysis Challenge

marcel_olmo — Tue, 23 Jul 2013 06:04:57 GMT

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.