Aggregation of an aggregation in a data table

[Peter Curnow]

Hey everyone,

I'm looking to add a calculated column to a data table to get an average of figure SALARY grouped by CODE1 and CODE2 and then get the average of each of those Average figures grouped by CODE 2. I'm trying to use an expression along the lines of:Avg(Avg([sALARY]) OVER ([code1],[code2])) OVER (CODE2]) but this includes duplicate average values from the first average in the second average calculation so the result is incorrect. I did try playing around with the [Value] and THEN functions but they don't seem to work with a data table too well.

This is how I would like it to work:

 

 

 

CODE1

CODE2

SALARY

Avg1

Avg2

 

 

XX

11

1000

1000

5250

 

 

XY

11

10000

9500

5250

 

 

XY

11

9000

9500

5250

 

 

TT

12

5000

3000

3500

 

 

QR

12

4000

4000

3500

 

 

TT

12

1000

3000

 

3500

 

 

 

 

But this is how it currently works - the Avg2 just ignores the first grouping and takes the average based on CODE2.

 

 

 

CODE1

CODE2

SALARY

Avg1

Avg2

 

 

XX

11

1000

1000

6666.667

 

 

XY

11

10000

9500

6666.667

 

 

XY

11

9000

9500

6666.667

 

 

TT

12

5000

3000

3333.333

 

 

QR

12

4000

4000

3333.333

 

 

TT

12

1000

3000

3333.333

 

 

 

Thanks

[Gaia Paolini]

Spotfire's calculations are not incorrect per se. Spotfire consider each row unless you tell it otherwise.

Try this:

first identify the first value of each Avg1

Avg1first: Rank([Avg1],[code1],'ties.method=first')

then average only over these:

Avg2: Avg(If([Avg1first]=1,[Avg1],NULL)) over ([code2])

or combined:

Avg(If(Rank([Avg1],[code1],'ties.method=first')=1,[Avg1],NULL)) over ([code2])

[Peter Curnow]

Thanks for your help. I tested out your method and just had to make one adjustment to the rank expression where I added [code2] as well so it looks like this:

 

Rank([Avg1],[code1],[code2],'ties.method=first')

 

Without it Spotfire just gives Ranks to the CODE1's and there are some cases where CODE1, CODE2 combinations don't contain a 1 ranking and so don't give a value for the Avg2 calculation (we want one value per CODE1, CODE2 combination).

 

Otherwise it looks to be working perfectly, thank you again!