8 Replies Latest reply on Jun 24, 2016 6:56 AM by Michael Lahey

# Filter 1 Column based on data from different column

Hi All,

I have been trying to figure this out myself for a few hours and can't seem to get it done correctly. I would like to exclude data in the ID column based on whether or not it has a Cancel task in the second column. Is there something with creating a set of both columns that could help? The data I'm working with is also tough to deal with since it isn't completely uniform (so I can't just do a min or max).

Any help would be appreciated, workbook attached.

• ###### 1. Re: Filter 1 Column based on data from different column

Hi Michael;

I was able to solve it this way:

I created a small LOD calculation:

Then I dropped this onto the Filters column and chose "False"

This prevented anything with a Cancel in Task from displaying.

Hope this works!

3 of 3 people found this helpful
• ###### 2. Re: Filter 1 Column based on data from different column

hi Michael,

Unfortunately I only have 9.0 and 9.3 still installed, so the attachment is in 9.3.

So yes (although there are a few ways we could do this) sets is my preferred method. For a conditional set we need it to be a Boolean test, and the test has to be an aggregate. This way we can create a test on and aggregate, but the set in/out will be returned at the id level...

So I created a set on ID

I then used the following formula as the condition of the set

So the inner part is putting 1 or 0 next to each row, depending of whether the row = 'Cancel'...it then takes the MAX of this by id...and if it =0 (i.e. the id has no task cancel) they get put in the set. As this is returned at id level, the entire id (not just the task = cancel row) is excluded. This is a very useful technique...so much so there is an entire video (with loads of great use-cases, and explaining this much better than I can!)

Think Data Thursday - November 20 - Let's talk about Sets Baby!

Hope this does the trick and makes sense. If not (on either front) please post back

2 of 2 people found this helpful
• ###### 3. Re: Filter 1 Column based on data from different column

...Just to let you know, mine and other Michael's solutions are actually the same....sets were the FIXED LoDs, before we had FIXED LoDs!! (I'm just a little old fashioned!!)

• ###### 4. Re: Filter 1 Column based on data from different column

Thank you both!

• ###### 5. Re: Filter 1 Column based on data from different column

This is great, Simon Runc. I want to learn a little more about sets ("Let's talk about Sets, Baby") so I can use them more easily in my day-to-day operations.

• ###### 6. Re: Filter 1 Column based on data from different column

My next step (that i thought I could handle myself ) is to get the max duration task per ID.

I tried doing an LOD calc, but can't seem to figure out what to use.    {INCLUDE [Task]:ATTR([Duration])=MAX([Duration])} -> can't use because ATTR is invalid in LOD

{INCLUDE [Task]: MAX([Duration])} -> just gives the max duration if there is more than 1 per task

Can't seem to figure out what calc to use for a set.

Any more insight on this?

• ###### 7. Re: Filter 1 Column based on data from different column

hi Michael,

So we can do this, as you've nearly done, with LoDs. In the below I've done this over a couple of formulas to help the understanding, but in your final version you can nest into one (if you like!)

So first we need to get the MAX Duration, by ID...so that we can later match this against the MAX task

[Max Duration - SR]

{FIXED [ID]: MAX(IIF([Exlude if Cancel],[Duration],NULL))}

btw I've added the check within the formula for the ID not having Cancel as one of their tasks. If you are using that set as a filter, you can ignore this bit and just becomes

{FIXED [ID]: MAX([Duration])}

So you can now see this puts the MAX duration task duration against every ID.

So we can then do a test on this

IF [Duration]=[Max Duration - SR] THEN [Task] ELSE NULL END

so this field only returns the Max duration Task...btw I've assumed no aggregation is required for the Duration (i.e. that there is only one row per ID/Task). This keeps all the calculations at row level, so work regardless of the vizLoD. If we did want to aggregate this, but keep the calculations at row level, we could create another LoD

I've shown what this looks like (filtering out non-max tasks) in the 'final' tab. You'll need to think how you want to handle where there are 2 max tasks for an ID

1 of 1 people found this helpful
• ###### 8. Re: Filter 1 Column based on data from different column

Ah, should have stuck with the logic and thought about the max per ID instead of task level.

Thanks again for the insight!