max value

[Sridharan]

hi,

is it possible to select a dimension value based on the maximum value of a particular dimension for each occurence of another dimension object in BO.

say for ex i have 4 dimensions d1,d2,d3,d4... here d2 is a date dimension and d4 can take only two values either 'A' or 'B'.. now i would like to display d3 value for maximum value of d2 with

respect to d1... what i want is to calculate the maximum value of d2 for each d1 and then display the corresponding d3 value for that maximum date.....

when i bring d1, max(d2) and d3 in the query panel it does the grouping by d1 and d3 which is not i'm looking at... first it should find the max(d2) for d1 and then display the d3 value corresponding to that d1, max(d2) combination....

thanks for your replies.

regards,

sri

 

[Sridharan]

i've got that report working the way i want it to...

sri

 

[blom0344]

Hello Sri,

By using the correlated subquery technique you can get the d1,d3 combinations for the maximum value of d2.

*********************************************************
Select t.d1,t.d3
from t
where( t.t2 = all
(select max (t_alias.t2)
from t t_alias
where t_alias.d1 = t.d1
group by t_alias.d1))

If you run a second query which retrieves d1, max(d2) you can link the two datasets over the d1 dimension.

Possible someone might throw us some slick calculation with a specific context, but I can not come up with one that does the job with one query specified.

Correlated subqueries tend to become very slow with large tables , so if this will perform............
T. Blom
Information analyst
tbl@shimano-eu.com

 

[sirajkhan]

Sri,
was the above solution the one you used?
or did you come up with something else?
i also have a very similar problem.

Siraj.

 

[Sridharan]

Siraj,

I used Correlated Sub Query to solve that problem.

Sri