Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Trouble getting Percentage Variance between 2 Values used to get by Timestamps in Obiee

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.
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
-
how to do you analize the change percetange variance, between days, hours? Could you give a sample of data of what do you want?
Kind Regards,
0 -
Basically, my results come back like this using the formulas
Current ... Previous
863 514
284 165
1698 1152
What i want to do is calculate the percentage difference between the results now. But when i do, i just get 1 or 100% back as a result when i try my using my theory in the above thread
Many thanks
Ashley
0 -
Ummm If you want to calculate the percentage the variance, you have to do something like this:
(1 - Previous Month / Current Month)* 100, these is the formula how you calculate your variance formula:
Current ... Previous Variance
863 514 +40%
284 165 +42%
1698 1152 +32%
Kind Regards,
0 -
Thanks, I tried that but unfortunately it gave me back a result of 100% . I have included the full formula below
Sum(DISTINCT CASE WHEN "- Participant Prospect Current"."Prospect Creation Date C" >= TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND "- Participant Prospect Current"."Prospect Creation Date C" < TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) THEN 1 ELSE 0 END) / Sum(DISTINCT CASE WHEN "- Participant Prospect Current"."Prospect Creation Date C" >= TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE ) AND "- Participant Prospect Current"."Prospect Creation Date C" <= 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) *100
0