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 getting Percentage Variance between 2 Values used to get by Timestamps in Obiee

Received Response
1
Views
4
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.

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

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    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,

  • 2942132
    2942132 Rank 1 - Community Starter

    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

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    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,

  • 2942132
    2942132 Rank 1 - Community Starter

    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