Suppose you have summarized Sales data by week like the following.
Now, you want to show the Sales amount this week, last month this week and last year this week. Here is how.
1. Create a column with the year part of Order Date.
Create a new column with the year part (e.g. 2019, 2020) of Order Date. Click the column header menu of the Order Date, choose “Extract” and “Year”.
Name it “year” and click “Run” to create a new column.
2. Create a column with the week number part of Order Date.
Create a new column with the week number part (1-52) of Order Date. Click the column header menu of the Order Date, choose “Extract” and “Week of Year”. Name it “weekno” and create a new column.
2 new columns look like the following.
3. Create a Number chart for the Sales amount of this week
Go to the chart view and create a new chart. Set the name to “This Week”. Choose “Number” for Type and “Sales” for Value. Click the filter to create a new filter.
Click the “Custom” tab and enter the condition following to choose the week that contains today. Sys.Date() is a function that returns today’s date, year function returns the year part of the date and week function returns the week number of the date.
year == year(Sys.Date()) &
weekno == week(Sys.Date())
Once you click “Run”, you will see the Sales amount this week.
4. Create a Number chart for the Sales amount of last month this week
Create another new chart. Set the name to “Last Month This Week”. Choose “Number” for Type and “Sales” for Value. Click the filter to create a new filter and enter the following condition. Sys.Date() - months(1) means last month today.
year == year(Sys.Date()) &
weekno == week(Sys.Date() - months(1))
Now you have a Number chart showing the Sales amount last month this week.
5. Create a Number chart for the Sales amount of last year this week
Create another new chart. Set the name to “Last Year This Week”. Choose “Number” for Type and “Sales” for Value. Click the filter to create a new filter and enter the following condition. Sys.Date() - years(1) means last year today.
year == year(Sys.Date() - years(1)) &
weekno == week(Sys.Date())
Once you click “Run”, you will see the Sales amount last year this week.
