When last year = X then Y for this year

Question

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.).

So all yellow marked cells must return 0 instead.

How do i do that?

Thanks!

Robin

4209109 · Answer

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

SonPat99 · Answer

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.