Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Trouble working out percentage in Obiee using timestamp formulas to bring back total value

Received Response
1
Views
1
Comments
2942132
2942132 Rank 1 - Community Starter
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

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

Answers