SELECT avg(DATEDIFF (d,a.testdate,b.offerdate)) from tblapp a, tbljob b
where a.testdate > '1/1/2000' and b.offerdate <= '8/13/2003' and
b.offerdate > '1/1/2000' and a.userid = b.userid and a.offer = 1
and a.owner in
(select ownerID from tblArea where areaclusterID in (select
areaclusterID from tblareacluster where marketgroupid in (select
marketgroupid from tblmarketgroup where superregionid=670)))

Is it possible to write an MDX for this .

--
Dev Noronha


Posted via http://dbforums.com

Yes. The area, cluster, and superregion would be a hierarchy and you could
simply restrict to super region 670 in the where clause. UserId and Offer
would be seperate dimensions also in the where clause. You'd then create an
query scoped calculated member to evaluate the average. This would use a
non-empty cross join over the filtered set of members of the day levels in
the Test Date and Offer Date dimensions. The days would be filtered to have
the date ranges specified.

- Matt Carroll
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"devnoronha" <member33654 (AT) dbforums (DOT) com> wrote