1 2 Previous Next 18 Replies Latest reply on Apr 27, 2016 5:40 AM by Rohan Malusare

# Another 'easy' LOD question.. or is it ?

I'm spinning my wheels on this and would really appreciate some help.

Find me the tallest pupil in each school.. but only if they are in class 'a'

 School Class Pupil height a a bob1 105 a a bob2 102 a b bob3 108 a b bob4 102 a c bob5 110 a c bob6 100 b a bob7 100 b b bob8 101 c a bob9 101 c b bob10 107 c c bob11 108 c c bob12 103 c c bob13 109 d a bob14 110 d a bob15 108 d b bob16 102 d b bob17 108 d c bob18 100 d d bob19 109 e a bob20 109 e a bob21 107 e b bob22 107
• ###### 1. Re: Another 'easy' LOD question.. or is it ?

This should give you the tallest height but not necessarily the pupil  {fixed [School]: max(if [class]='a' then [height])}

• ###### 2. Re: Another 'easy' LOD question.. or is it ?

hi Timothy,

Love a good LoD challenge!...How's this for you?

{FIXED [School]: MAX(IIF([Class]='a',[height],NULL))}

• ###### 3. Re: Another 'easy' LOD question.. or is it ?

Great responses to get you the max height - however you asked for the tallest pupil, which I guess means the name. In which case there's more work to do...

Assuming the calculation supplied twice above is called MaxHeight, use that in another calc field: if [Height] = [MaxHeight] then [Pupil] END. That will return the name of the tallest pupil.

If you also want to return that name against each row of data for the school you would need to use a table calculation using the calculated field returning the pupil name. There are a number of ways to do that, one option being WINDOW_MAX(MAX([AboveCalcReturningPupil]))

You would need to set the Compute using to Address School, Class and Pupil, Restarting every School.

• ###### 4. Re: Another 'easy' LOD question.. or is it ?

Hi Timothy,

Please find the workbook where I have done the calculation to get the max height student from any school who only belongs to the class A.

In this workbook, I have calculated the height by Class, School and Pupil (refer - Calc_height_by_C_S_P)

Then I have calculated the maximum height by class and school (refer - Calc_height_By_C_S)

I have created the Boolean calculation which exclude the student which is having the height less than Class and school. (Refer - Bool_Max_Height)

Thanks and Regards,

Rohan Malusare

1 of 1 people found this helpful
• ###### 5. Re: Another 'easy' LOD question.. or is it ?

Hi Simon,

i tried using the calculation that you mentioned but it is not giving the desired result.

where as mine is giving the exact answer which Timothy require.

Please refer to the below screenshot.

Thanks and regards

Rohan Malusare

• ###### 6. Re: Another 'easy' LOD question.. or is it ?

Hi Timothy,

Is this what you are looking for?

I have attached the workbook solution . Let me know if this meets your requirement.

Thanks,

Rishabh

• ###### 7. Re: Another 'easy' LOD question.. or is it ?

Hi Andrew,

have you checked if solution that you suggested is working?

Regards,

Rohan Malusare

• ###### 8. Re: Another 'easy' LOD question.. or is it ?

Hi Rishabh,

I am not able to open your workbook due to version issue , can you please share your calculation what you have used in the workbook?

Regards,

Rohan Malusare.

• ###### 9. Re: Another 'easy' LOD question.. or is it ?

Somebody drank a lot of coffee this morning

• ###### 10. Re: Another 'easy' LOD question.. or is it ?

Hi John,

I have tried your solution but this is giving me following output. Its now filtering the required result. It is giving Max height for all the pupil in the school where the output should be the only student who has maximum height.

Regards,

Ashish Chaudhari

• ###### 11. Re: Another 'easy' LOD question.. or is it ?

Yes Rohan, I'm quite sure it works :-)

Step by step:

Result:

• ###### 12. Re: Another 'easy' LOD question.. or is it ?

Hi Timothy,

I think whatever solution that Rohan Malusare has provided is the desired result. I have taken the same workbook that he has uploaded and tried improving the same.

I have edited the following calculations.

Calc_height_by_C_S_P

({ FIXED [Class],[School],[Pupil] : SUM(if [Class]="a" then [Height] END)})

Bool_Max_Height

(({ FIXED [Class],[School],[Pupil] : MAX([Height])})>={fixed [Class],[School] : MAX([Height])}) and [Class]="a"

Actually, Rohan has applied the quick filter for the sheet which I have included in the calculation itself. (I have removed the filter for the class='a')

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 13. Re: Another 'easy' LOD question.. or is it ?

Ashish, You have added some of your own interpretation to the Timothy's requirement when you say "filtering" which may or may not be what is needed.  Also I would not have created the table in the way you have, but rather I would have positioned it in the header to the left and labeled it as tallest height so as not to cause confusion of each individual student height.

• ###### 14. Re: Another 'easy' LOD question.. or is it ?

OMG ... I've only just come back from meetings/lunch to find all this.. you fellas are fantastic !!!

It'll take me the rest of the afternoon to work out which solution fits my real problem (it's not about schools.. it's about clinical trial data )

1 2 Previous Next