Minimum within a Group Exclusion

[LawrSam12]

Hello,

I have a table of data

Order Reference	Task	Task Stage	Order Stage
ABC	111	1	2
ABC	121	2	2
ABC	131	3	2
ABC	141	4	2
CDE	111	1	1
CDE	123	1	1
CDE	122	3	1
FGH	111	1	2
FGH	144	2	2

Where the Order Stage is calculated as the minimum Task Stage, excluding the Stage associated with Task 111. All other data is given in the universe.

I have a formula currently to calculate the minimum Order Stage over all Task Stages, which is:

aggr(min(TOTAL <[Order Reference]> [Task Stage]),[Order Reference],[Task Stage])

But I need a way to exclude the Task Stage associated with one specific task, in this case 111.

Is there a way to do this?

Thank you.

[Lisa_P]

Try excluding the Task using =-{111}
aggr(min(TOTAL <[Order Reference], [Task]=-{111}> [Task Stage]),[Order Reference],[Task Stage])

[LawrSam12]

Thank you for your response.

Unfortunately that doesn't work, it just brings back nothing at all

[sunny_talwar · Accepted solution]

Try this

Min(TOTAL <[Order Reference]> {<Task -= {'111'}>} [Task Stage])

If Order Reference and Task (or Task Stage) are your only dimensions... then you don't really need Aggr() function here..

[LawrSam12]

Thank you so much Sunny, that's perfect.

I've just discovered that there's more than one task which I need to exclude, but they all have the same beginning title, lets say they're

111a

111b

111c

111d, etc.

Is there a possibility of including a Wildmatch function in here instead of excluded just one? I'm struggling with the syntax

[sunny_talwar]

Sure, try this

Min(TOTAL <[Order Reference]> {<Task -= {"111*"}>} [Task Stage])