Forum Discussion

EricB10's avatar
EricB10
Cloud Apps
03-04-2022
Solved

How to create Custom Columns: ThisWeek, ThisWeekPrevYear?

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!

data models
  • EricB10's avatar
    EricB10
    04-02-2022

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

     

4 Replies

Replies have been turned off for this discussion
  • EricB10's avatar
    EricB10
    Cloud Apps
    03-04-2022

    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's avatar
    ronenavidor
    Sisense Employee
    03-16-2022

    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's avatar
    EricB10
    Cloud Apps
    03-17-2022

    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's avatar
    EricB10
    Cloud Apps
    04-02-2022

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