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

When last year = X then Y for this year

Received Response
1
Views
2
Comments
4209109
4209109 Rank 1 - Community Starter
edited Aug 13, 2024 5:19PM in Oracle Analytics Cloud and Server

Hi all, i'm fairly new to BI and using the complex SQL within a column formula of OBIEE. So bear with me if i don't use the correct terms but i really hope you can help with my query!

I want to do the following. I work in Retail and for our stores we count visitor data (footfall) and eventually if someone buys something, that's a transaction. Together (trans / footfall) that makes conversion.

Okay, now i want do this for instance:

I want to show a week (per day) and for every day TY (this year) where the result of last year was above 0 it will show for TY on this day the footfall result. Then eventually i want to sum this for a period, in this example it's 1 week, but of course only to sum those days that have a Like For Like result (so condition = for every day a year ago there is data, it will show data this year, otherwise zero).

How this possible? I can't seem to get it to work, because all is conflicting (days / filter / etc.).

pastedImage_4.png

So all yellow marked cells must return 0 instead.

How do i do that?

Thanks!

Robin

Answers

  • SonPat99
    SonPat99 Rank 6 - Analytics Lead

    Hey Robin,

    You can definitely achieve this using CASE Statement with TIMESTAMPADD function.

    Before that, I would like to understand what you have done so far, like what formula you are using currently, filters, etc.

  • 4209109
    4209109 Rank 1 - Community Starter

    Hi SonPat99, Thanks for helping out already!

    This is Footfall LY COND column:

    FILTER(CASE WHEN SUM("F: Sales Transaction"."# Transactions"/"F: Sales Transaction"."Footfall" BY "D: Stores"."O Store","Fiscal"."Date") > 0.5 THEN "F: Sales Transaction"."Footfall" ELSE 0 END USING ("Fiscal"."Date" BETWEEN date '2019-03-03' AND date '2019-03-09'))

    This is Footfall COND column:

    FILTER(CASE WHEN SUM("F: Sales Transaction"."# Transactions"/"F: Sales Transaction"."Footfall" BY "D: Stores"."O Store","Fiscal"."Date") > 0.5 THEN "F: Sales Transaction"."Footfall" ELSE 0 END USING ("Fiscal"."Date" BETWEEN date '2020-03-01' AND date '2020-03-07'))

    So it's a same week comparison (sunday to saturday), but i just took 1 week as an example. For the example sake you can use any date range you want.

    Also:

    SUM("F: Sales Transaction"."# Transactions"/"F: Sales Transaction"."Footfall" BY "D: Stores"."O Store","Fiscal"."Date") > 0.5

    This part is needs to be changed somehow (at least i think) to check if Footfall Data last year same day was 0 or not and if 0 then This Year Footfall should not read any number, but just be 0 as well.

    I just used above condition as an example but has nothing really to do with my question.

    Hope this all makes sense.

    Thanks,
    Robin