MAX function or #MULTIVALUE

building reports webintelligence
1

Hi, Below is how my data look like. I can’t edit universe at this moment.


Air ID	Schedule Dt	Due Date	Status
1449	5/31/2013	5/31/2013	D
1449	4/30/2013	4/30/2013	P
1556	5/31/2013	6/30/2013	P
1556	4/30/2013	5/31/2013	D

I need to show MAX(Schedule Dt) based on Air ID. I need to do this at report level only. Below is my result set;


1449	5/31/2013	5/31/2013	D
1556	5/31/2013	6/30/2013	P

I created a variable of MAX(Statement Dt) and then used with where (due date=statement DT) for Due Date column but no luck. Appreciate your help. Thanks,

wack_jack (BOB member since 2005-12-28)

2

First make a variable which returns 1 if it is the highest schedule date in the air id and 0 if it doesn’t.

=If(Max([Schedule Date]) In ([Action ID])=[Schedule Date];1;0)

The variable will become a measure due to the usage of a max function. This blocks you from filtering it.

You can however set a top 1 ranking on this variable, which should do the trick.

Andy

andy.v :belgium: (BOB member since 2008-12-19)

3

Thanks for the reply. So I created two variables
(1) Max([Schedule Date]) In ([Action ID]) Called ‘MAX SD’
(2) If([MAX SD]=[Schedule Date];1;0) Called ‘SD Ind’
Now I don’t have any measure object in report so while applying ranking it shows based on drop down list as MAX SD and Ranked by SD Ind but its not working for some reason. Report or block still shows all 4 records . When I put ‘SD Ind’ variable to the block it shows correct indicator either 1 or 0 though.
Thanks,

wack_jack (BOB member since 2005-12-28)