When last year = X then Y for this year

4209109

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

SonPat99

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

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