First day of the week

Techy007

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.

tadashii

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

 

Techy007

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

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

Like this 

tadashii

Hi there - Assume your daily data looks like this

Daily Data

 

 

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

Dim Date

 

 

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

 

 

 

Summarize by WeekNoCustom in Analyzer

Analyzer

Hope that helps ?

 

EF1218

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! 

Techy007

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

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