topic Minimum within a Group Exclusion in QlikView

Minimum within a Group Exclusion

LawrSam12 — Sat, 16 Nov 2024 20:47:11 GMT

Hello,

I have a table of data

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 — Mon, 27 May 2019 23:15:19 GMT

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

LawrSam12 — Thu, 30 May 2019 09:00:21 GMT

Thank you for your response.

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

sunny_talwar — Thu, 30 May 2019 12:19:30 GMT

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 — Fri, 31 May 2019 08:34:14 GMT

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 — Fri, 31 May 2019 11:29:18 GMT

Sure, try this

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