Business Day Calcuation Measure On two Differenet measures

[Former Member]

Need to calculate BD(bisineess day) between two dates.

BD validation is stored in one table( calendar ) and Other dates in another table ( Policy)

For One calculation the dates have to be between Entry date to load date
For Other Calcution the dtaes have to be between Chnge date to load date.

I have created two aliases fro the calendar table at universe Level and created a  complex join to apply a case and sum when the date is between Entry - load date.

Similarly Complex join between and case Sum when date is between change- load date

so that I can keep the Above two measures sepatarly.

Problem is  Individually they are coming correct,When Both aliases are coming into picture , the measure values are not coming corrcet theu are coming as more then what is expected .

Below is the SQL which is generated from SQL

SELECT

A, B, C, D,E, F

   SUM(Case When Calendar1.BDIND ='Y' then 1 Else 0 END),

   SUM(Case When Calendar2.BDIND ='Y' then 1 Else 0 END)

FROM

  CALENDAR_MAIN  Calendar1 RIGHT OUTER JOIN   Policy ON (Calendar1.calendar_date >= policy.Enter_time  AND Calendar1.calendar_date < policy.createtime)

     RIGHT OUTER JOIN CALENDAR_MAIN  calendar2 ON (calendar2.calendar_date >= Policy.change_time  AND calendar2.calendar_date < Policy.createtime)

     WHERE

  (

   Policy.createtime  IN  ( {d '2011-05-01'}  )

   AND

   Policy.DESC  IN  ( 'A',' B'  )

  )

GROUP BY

  1,

  2,

  3,

  4,

  5,

  6,

  7,

[Former Member · Accepted solution]

why don't you use combine queries while creating WEBI reports??

Just create two queries for each alias and then combine it using union operator?

If you got solution then ok else provide some snapshot of data which you have and what you want to achieve.

Thanks,

SB

[Former Member]

yup Was able to figure Out can be done throgh Merging the Dimensions and keeping measure s , differently , Plus the SQL parameter Multiple sql for Each measure will do the trick

[Former Member]

great then if you got your answer then mark my answer as correct and close the thread.