Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

aggregation help

Hi Experts,

Can any one please help me on below issue.

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 below attached image. Please help me to get the related percentage values of aim1 and aim2 without filtering.

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

results.png

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 )

)

Thanks in advance.

3 Replies
ogautier62
Valued Contributor II

Re: aggregation help

Hi

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

Because with your formula it adds scope of other names

Regards

mahitham
Contributor

Re: aggregation help

Hi ogautier62,

Thanks for your reply.

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

mtucholski
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 )

)

Community Browser