Hello,

Im having trouble working out a percentage in OBIEE. Im trying to work out what the variance level is between current Month and Previous month.

I have created 2 formulas using time stamps to get my overall value back.

Current Month

Sum(DISTINCT CASE WHEN "XXXXX" >= TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE ) AND "XXXXX" <= TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) THEN 1 ELSE 0 END)

And

Previous Month

Sum(DISTINCT CASE WHEN "XXXXX" >= TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND "XXXXX" < TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) THEN 1 ELSE 0 END)

My theory is to take (Current Month Formula) / (Previous Month Formula) *100. When  do this it just brings back a 1 or 100%.

Any suggestions?

Thanks