Aggregation

[MindaugasBacius]

Hello,

I do have a Pivot table. I wish to get the "Team * order out" expressions right.

The aggregation have to be made according to Order and Team. Also if this expression If(Type = "KG" and Fact/Plan > 0.9, 1, If(Fact/Plan = 1, 1, 0))  returns 0 than the whole order lines have to become 0. All expressions in Set analysis manner and in front end.

The out come I would like to get:

This is the logic that stands behind:

If any questions arises please ask.

Thank you for cooperation!

[kuba_michalik · Accepted solution]

In the attachment, you will find some sort of a solution that meets your preferred conditions... but to be honest, it was a nice exercise in using Aggr, but it's kind of ridiculous (kudos to you if you can understand what that formula is doing, I'm pretty sure I myself would have problems with it in a week time )

It would make much more sense to me to calculate whether order can be counted as "out" or not in script (with group by Order and Team), and then use such flag in your formulas.

[sunny_talwar]

May be another option

1) Max(TOTAL <Order, Team>{<Type = {'KG'}>} If(Fact/Plan > 0.9 and Team = 1, 1, 0))

2) Max(TOTAL <Order, Team>{<Type = {'KG'}>} If(Fact/Plan > 0.9 and Team = 2, 1, 0))

3) Max(TOTAL <Order, Team>{<Type = {'KG'}>} If(Fact/Plan > 0.9 and Team = 3, 1, 0))