11 Replies Latest reply on Nov 21, 2018 5:51 AM by timothy rafferty

# LOD within a LOD

Could somebody help me out with the attached.

Imagine a lot of 'things' that belong to only one 'group'.  The thing can have any value.. but the group has - lets say - a 'bar'.

Which thing has the max value ... and which thing (or things) has the max value under the group's 'bar' ...

So.. if you have

thing1 / thingVal = 32 / GroupA / GroupA_Bar = 20

thing2 / thingVal = 2  / GroupA / GroupA_Bar = 20

thing3 / thingVal = 19 / GroupA / GroupA_Bar = 20

thing4 / thingVal = 15 / GroupA / GroupA_Bar = 20

thing5 / thingVal = 19 / GroupA / GroupA_Bar = 20

thing6 / thingVal = 99 / GroupA / GroupA_Bar = 20

I want to flag thing6 as the the thing with the biggest value in the group  (that's easy... if [Thing Value]={ FIXED [Group]:MAX([Thing Value])} then 'max for group' END)

But.. I also want to flag thing3 and thing5 (they have the max value which is 'below the bar')

And I need it all on one worksheet (there is an 'easy' solution to split the data (see below))

I've tried and failed a few approaches and now my brain is frazzled

(I thought this would work.. but it gives me a rogue answer per group...... IF [Thing Value]={ FIXED [Group],[Thing Value]<[Group Bar]:MAX([Thing Value])} then "yes" else "no" end)

• ###### 1. Re: LOD within a LOD

Hi Timothy,

great question. For the second part, do you want to know which is the highest value but also smaller than the Bar value or do you want to find out if any of the highest values is ALSO under the bar. I think from your PSEUDO code and description I am not sure.

• ###### 2. Re: LOD within a LOD

Your pseudo code is confusing. Please post your expected output in table form for attached sample workbook.

• ###### 3. Re: LOD within a LOD

Thanks for the replies...

I've simplified the original post.. hopefully it now makes more sense

• ###### 4. Re: LOD within a LOD

Hi Timothy,

Your edited question helped to understand what you need.

Find attached a workbook with Part 1 fo identifying things that have the highest Group value and Part 2 which have the highest group within the group that are under the Group Bar.

Let me know if this helps.

• ###### 5. Re: LOD within a LOD

Try this :

{FIXED [Group]:

MAX( IF [Thing Value]=

{FIXED [Group]:

MAX( IF [Thing Value] < { FIXED [Group]: MAX([Group Limit])}

THEN

[Thing Value]

END

)

}

THEN

[Thing]

END

)

}

• ###### 6. Re: LOD within a LOD

Really impressive ! But.. not quite

That only returns one value .. one Thing that has the max value under the bar.

But multiple Things might have the same max value under the bar (sorry, I didn't specify that in the original post.. have just amended)

• ###### 7. Re: LOD within a LOD

Ah.. again.. I should have stipulated.. it has to be on one worksheet because the real (medical) data is way too complicated for 2 sheets

(I honestly had thought of this 'two part solution' )

• ###### 9. Re: LOD within a LOD

Hi... I've amended the original post... (not the code).. your solution was pretty close.. but it was only returning one 'thing' (using Max).. I couldn't work out how to amend your code to account for the case where more than one thing has the max value under the bar

• ###### 10. Re: LOD within a LOD

Drag Group and Thing into the rows shelf.

Create this field and put on filter shelf and select True.

{FIXED [Group]:

MAX(IF [Thing Value] < { FIXED [Group]: MAX([Group Limit])}

THEN

[Thing Value]

END

)

}=[Thing Value]

• ###### 11. Re: LOD within a LOD

wow....

That's very cool.. and the correct answer.. I honestly don't think I could have come up with that !

(as always with these types of problems.. I'm left wondering if Tableau is my best tool... in my real world example I have hundreds of thousands of rows.. this 'double looping' through all the values can't be that efficient)

Cheers Ombir.. you're a guru