New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

aggregation help

Hi Experts,

I have calculated CC1% and CC2% with below formulas.

Here Each Name contains multiple SCOPE values. by default CC1%   and CC2% are showing wrong values but when I have filtered one particular Name =aim1 getting correct percentage values.

Please find the attached sample app and sample excel with calculations in the second sheet.

CC1% :

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

CC2%:

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

3 Replies
Valued Contributor II

Re: aggregation help

Hi

May be try sum(total <date, scope, name>

Regards

Contributor

Re: aggregation help

Hi ogautier62,

When I have applied sum(total <date, scope, name> in the CC1% the percentage values are reducing.

Contributor

Re: aggregation help

Hi,

there is something wrong with denomiator. Have you tried this?

CC1% :

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL <Name>

Aggr(

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

CC2%:

Sum(TOTAL <SCOPE>

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)

/

Sum(TOTAL <Name>

Aggr(

RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

, Date, SCOPE, Name )

)