Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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..
Thank you for your response.
Unfortunately that doesn't work, it just brings back nothing at all
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..
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
Sure, try this
Min(TOTAL <[Order Reference]> {<Task -= {"111*"}>} [Task Stage])