topic Re: Trouble with Aggr in pivot table in QlikView

Trouble with Aggr in pivot table

jarrad_murray — Fri, 05 Jan 2018 10:52:34 GMT

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 — Fri, 05 Jan 2018 12:55:33 GMT

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 — Fri, 05 Jan 2018 13:59:05 GMT

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