First day of the week

Techy007
Techy007 Member
in Magic ETL

Hello. I have an excel output which has data fed everyday. that contains all information about how many sales we made everyday. I would like to put on a dashboard that gives ne weekley information. So how can I code in magic ETL/ Beat Mode. So I can make my first day of the week as Sunday. eg: August 25th 2019 to August 31st as my week. So how can I automate or code for 52 weeks.

Comments

  • tadashii
    tadashii Member

    HI - I would probably use another table which contains all the date info and join to your daily sales data using the date key. The date table will probably look like this. So 25 Aug - 31 Aug 2019 will belong to week 35. 

     

    Alternatively, in beast mode, try WEEKOFYEAR(`Date`)

     

    dim date.png

  • Techy007
    Techy007 Member

    Please elaborate, how can I code this in SQL? If I understood it correctly did you mean, I make one separate sheet which contains only date of sales and then join that to whole data. .But how do I code in SQL?

  • Techy007
    Techy007 Member

    Also my spreadsheet  has data from 2010, I need to formulate this from 20181230- 2019-0106 hence forth.

    Like this 

  • tadashii
    tadashii Member

    Hi there - Assume your daily data looks like this

    Daily DataDaily Data

     

     

    And you set up your Dim Date as below. Note the WeekNoCustom column at the end

    Dim DateDim Date

     

     

    Then join both datasets using Magic ETL. You may want to change the WeekNoCustom's data type to string to make it easy.

     

    Magic etl.png

     

     

    Summarize by WeekNoCustom in Analyzer

    AnalyzerAnalyzer

    Hope that helps ?

     

  • EF1218
    EF1218 Member

    I have had a similar issue where I had daily snapshots, and when I graphed by week they would all aggregate and show incorrect numbers. To get around this, I created a Beastmode filter at the card to only include snapshots on the first day of the week (see attached). Then, on the card, I filtered on this calculated field to only include 'Weekly Snapshot'

     

    I hope this helps! Weekly Snapshot.PNG

  • Techy007
    Techy007 Member

    Thanks for explaining it so nicely, appreciate it !

    I am curious as if I split the dim date, how will it get automated then? As all the data is sitting in one spreadsheet only. As this sales information will be coming everyday, the scheduled file is updated everyday. How will Dim date get updated then ?

  • tadashii
    tadashii Member

    Hi - You can set up the dim date in various ways:-

    1) append one year of data to the dim date (manual every year)

    2) append 100 years of data to the dim date (manual one off)

    3) write a SQL procedure to populate the dim date (auto yearly)

     

    The magic ETL that does the join will simply take the daily sales data ( that gets updated every day and joins to the dim Date ). You can set the ETL to run whenever the sales spreadsheet gets updated. 

     

    As your weekNo requirement is different from the built-in function (WeekOfYear(date)), you need to do a customWeekNo. Otherwise, you need to use some complex rules in the beast mode

     

    WeekOfYear.PNG

     

Categories