Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
LawrSam12
New Contributor

Minimum within a Group Exclusion

Hello,

I have a table of data

Order ReferenceTaskTask StageOrder Stage
ABC11112
ABC12122
ABC13132

ABC

14142
CDE11111
CDE12311
CDE12231
FGH11112
FGH14422

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.

1 Solution

Accepted Solutions

Re: Minimum within a Group Exclusion

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..

View solution in original post

5 Replies
Employee
Employee

Re: Minimum within a Group Exclusion

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

Re: Minimum within a Group Exclusion

Thank you for your response.

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

Re: Minimum within a Group Exclusion

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..

View solution in original post

LawrSam12
New Contributor

Re: Minimum within a Group Exclusion

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

Re: Minimum within a Group Exclusion

Sure, try this

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