Period over Period card, Last 'n' weeks compared to 52/53 weeks ago = How to EXCLUDE current week?

dean_pangelinan
dean_pangelinan Member
in Magic ETL

I have a BigQuery dataset that updates daily, but is one day behind on purpose.

Today is Monday, so the lastest data that I have is from Sunday.

 

My company is working on a Mon-Sun fiscal calendar, and, yes, it IS optioned for our instance.

I am creating a "Last 5 Weeks" grouped bar card, with comparison enabled to 52 weeks prior for my metric of choice, with the x-axis specified as the 'Date' (a date-time variable.)

 

When I choose a vertical bar displaying the last 2 weeks, by day, no data shows for Today (MONDAY), but data does show for Yesterday (SUNDAY), as expected.

 

However, when I change my visualization to a "Last 5 Weeks" view by week, with the Mon-Sun fiscal calendar optioned, and select the "grouped bar" option for a period over period card, the following occurs:

 

With my "Category Scale" set to default, with blank "Date Output Format" and either a DEFAULT choice or a CHOICE of TRUE for "Never Use Time Scale", there is a column that shows for "THIS" week with TODAY'S date (MONDAY) as yyyy-mm-dd.

 

With my "Category Scale" set to default, with blank "Date Output Format" and the choice of FALSE for "Never Use Time Scale", data for LAST WEEK / THIS YEAR shows aggregated across MON-TUES-WED of THIS year, data for LAST WEEK / LAST YEAR shows aggregated across FRI-SAT of LAST year, and data for THIS WEEK / LAST YEAR shows aggregated across FRI-SAT of LAST year.

I believe this to be an error in visualization, as the x-axis shows as a continuous string broken by individual dates, with the MONTH YEAR showing below that string.


IF I do as I was recommended by Tech Support in previous calls, and option the "Date Output Format" to either 'MM.dd' or 'MMM-dd' with the "Never Use Time Scale" menu optioned to EITHER 'True' OR 'False', I still get an incorrect count of values showing for TODAY (MONDAY) either as '01.yy' or 'Jan-<TODAY>' -- that is '01.14' or 'Jan-14' when NO data has been accumulated.

HOW can I get RID of the visualization of ANY data for THIS week, and ensure that the data for SUNDAY is counted as part of LAST week?

 

Comments

  • Valiant
    Valiant Coach

    So it sounds like something is getting crossed up using Mon-Sun as your week range. With that being the case, I'd look to apply a calculation to my dataset to determine the week number myself. And hard code the 52 week over week comparison.

     

    You could set it up like this: (Week() function documentation: https://www.w3resource.com/mysql/date-and-time-functions/mysql-week-function.php)

     

    SELECT *, WEEK(`Date`,1) as 'Week_of_Year'
    FROM dataset
    WHERE CASE WHEN WEEK(CURRENT_DATE(),1) = WEEK(`Date`,1) 
                  AND `Date` >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) 
          THEN 0 
          ELSE 1 
          END = 1

    This will return all data where the date is not in the current week. 

     

    From there you should be able to format your new Week_of_Year column and use it as your X axis. If you're doing week over week, you may also need to add beast modes at the card level, one for "this year' and one for "last year".

     

    Let me know if you need any more detail or have questions about how to get started.

     

    Hope this helps,
    Valiant

     

     

     

     

     

     

Categories