Trouble with Aggr in pivot table

[jarrad_murray]

Hi All,

I am having difficulty with some aggregation within a pivot table (attached).

Currently column 3 displays the value 15,360.85 when nothing is selected, as a month, CCG or specialty are selected the value amends itself.

What I am trying to achieve is for column 3 to show a single value (the same value for all specialties) for each CCG and Month

e.g.

April

     06L - 100 - 27.02

     06L - 101 - 27.02

     06L - 110 - 27.02 etc.

     06Q - 155.78

     06T - 1389.88

     07G - 0.80

May

     06L - 23.37

     06Q - 134.76

     06T - 1211.72

     07G - 0.69

I have tried changing the aggregate statement but don't seem to be able to achieve what I am after.

If possible, can someone tell me where I am going wrong and what I need to do to fix this please.

Your help is greatly appreciated.

[sunny_talwar · Accepted solution]

Try this

Sum( total <MonthName, CCG_Code>

Aggr(

if(

(if(

CCG_Code = '06T', sum({$<Spec -= {'INCOM92%'}>}RTTTotal),

sum({$<Spec -= {'INCOM92%'}>}RTTTotal)*max(Excl06T)) * 0.92)

- if(

CCG_Code = '06T', sum({$<Spec -= {'INCOM92%'}>} [<18]),

sum({$<Spec -= {'INCOM92%'}>}[<18])*max(Excl06T)

) < 0, 0,

(if(

CCG_Code = '06T', sum({$<Spec -= {'INCOM92%'}>}RTTTotal),

sum({$<Spec -= {'INCOM92%'}>}RTTTotal)*max(Excl06T)) * 0.92)

- if(

CCG_Code = '06T', sum({$<Spec -= {'INCOM92%'}>} [<18]),

sum({$<Spec -= {'INCOM92%'}>}[<18])*max(Excl06T)

)

)

,MonthName,CCG_Code, Spec))

[jarrad_murray]

Hi Sunny,

Thank you, that has fixed the issue I was facing.  I forgot you could specify the total dimensions within the statement.

Thank you very much