How to create Custom Columns: ThisWeek, ThisWeekPrevYear?

[EricB10]

Hi,

I am trying to create two boolean Custom Columns in my DimDate table to flag data in the current week, and data in the same week, previous year.  This type of flag is easy to create for a monthly resolution, but weeks are tricky because they can span across two months/years.

Below is the code am using for ThisWeek which works except for when the week spans across two years (last week of the year).  The only change for ThisWeekPrevYear is using GETYEAR(CURRENTDATE())-1

CASE
    WHEN WEEKOFYEAR(DATE) = WEEKOFYEAR(CURRENTDATE())
    AND DAYOFWEEK(DATE) <= DAYOFWEEK(CURRENTDATE())
    AND GETYEAR(DATE) = GETYEAR(CURRENTDATE())
    THEN 1
    ELSE 0
END

There is an additional complexity using WEEKOFYEAR in the case of a leap year (53rd week).  Any suggestions on how to fix this code to account for these two issues, or perhaps a different approach entirely?

Thank you!

[EricB10]

Sorry, I didn't describe my columns correctly.  What I'm actually looking for is to flag rows that are in Week-To-Date, and the same days previous year.
IE:

(Monday - Today)
(Monday - Today, PrevYear)

Thanks again

[ronenavidor]

Hi,

You will need to add a custom column to flag is a specific date is within the range of Monday - Today for every row, so everything you build the EC is will get updated. Let say 1 to include, meaning it is M-Today and 0 not. Use this as Widget filter to start with. 

You can then in your formula use a formula filter to calculate the value 53 week ago (Use the advanced option), and you code will look like the below:

{
"last": {
"count": 1,
"offset": 53
},
"custom": true
}

The above formula together with M-Today filter will give you what you are asking. Hope this helps

[EricB10]

Hi @ronenavidor,

Thank you for your reply!  The custom column you suggested is exactly the piece I am struggling with.  The code in my OP works in most cases, except when current week overlaps two years - current year and previous year.  This case will occur on the first week of every year, unless Jan 1 in a Monday.

I'm also unsure how your filter code addresses the leap year issue - shouldn't "offset" be 52 in most cases, unless previous year was a leap year (then 53)?  I didn't think Sisense's JAQL filters were so dynamic to handle this logic, please correct me if I'm wrong.  This is why I was opting for the appoach of two custom column flags - thisWeek, thisWeekPrevYear.

I would love to see a built in tool to handle these common date comparisons, but any other suggestions are most welcome.  Thank you!

[EricB10 · Accepted solution]

If anyone is interested, I did eventually find a solution!  This leverages a Common Table Expression in Snowflake, so I don't believe this approach would be possible using only Sisense's data modeling tools.

Note: in addition to WTD and WTD_PREV_YR, this also contains code to create flags for MTD, MTD_PREV_YR, YTD, YTD_PREV_YR.

create or replace view DIM_DATE_NEW as (
    with comparison_date as (
        select
            case
                when weekofyear(current_date()) = 53
                then 52
                else weekofyear(current_date())
            end as comp_week
            , date as comp_date
            from <TABLE>
            where year(comp_date) = year(current_date()) - 1
            and weekofyear(comp_date) = comp_week
            and dayofweek(comp_date) = dayofweek(current_date())
    )
    select distinct
        d.date
        , case
            when weekofyear(date) = weekofyear(current_date())
            and date - current_date() <= 0
            and date - current_date() >= -6
            then 1
            else 0
        end as WTD
        , case
            when weekofyear(date) = weekofyear(c.comp_date)
            and date - c.comp_date <= 0
            and date - c.comp_date >= -6
            then 1
            else 0
        end as WTD_PREV_YR
        , CASE
            WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
            AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE())
            THEN 1
            ELSE 0
        END AS MTD
        , CASE
            WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
            AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
            THEN 1
            ELSE 0
        END AS MTD_PREV_YR
        , CASE
            WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE())
            THEN 1
            ELSE 0
        END AS YTD
        , CASE
            WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
            AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
            THEN 1
            ELSE 0
        END AS YTD_PREV_YR
    from <TABLE> as d
    join comparison_date as c on 1=1
    order by date desc
);