Date Calendar in Visual Analytics - Is it possible?
Some time ago, there was a customer who asked about how to make a Date Calendar in Visual Analytics (VA) and not at least if it was possible at all. She had tried to Google it, but just found posts from Power BI and Tableau on the subject. We discussed a bit, and ended up finding an easy way of doing this in VA. So, this post is to help others make use of a Date Calendar view in SAS Visual Analytics.
I will go through three different ways of making a Date Calendar. The data used consist of the highest Apple Stock Prices from Nasdaq for each day in every month over several years. If you wish to make a Date Calendar with these data, then you could download them from Kaggle here:
After downloading the data, then upload the csv-file of Apple stock prices called AAPL.csv. This file consist of the following columns:
- Ticker: Indicating which company the prices is for.
- Date: Consisting of the dates with the format Year-Month-Day.
- Open: The opening price of the stocks.
- High: The highest price of the stocks.
- Low: The lowest price of the stocks.
- Close: The closing price of the stocks.
The csv-file look like this (only showing the first nine observations):
Date Calendar for the first half year
First, I will show how to make a Date Calendar for the first half year step-by-step in VA.
Step 1: Before we start with the visualization of a Date Calendar, there are some need to calculate new variables from the Date variable.
A problem in VA, is often that dates don't have the proper Date format when uploaded to VA. So, often you need to calculate a new variable to get the correct format. The first step is clicking Calculated item below New data item, which can be found by choosing the Data pane on the left side in VA. To be able to get the correct format, then use the nested calculation of different functions as showed below:
- DateFromMDY(Parse(Substring(date,9,2), 'NUMX32'),Parse(Substring(date, 6, 2), 'NUMX32'),Parse(Substring(date, 1, 4), 'NUMX32'))
Remember to call the new variable Date and choose the format DDMMYYYY.
After that, calculate some more variables by choosing the different functions written below in Italic :
- DayOfWeek = DayOfWeek(Date), format=Numeric(Best12.).
- WeekNB = WeekNumber(Date), format=Numeric(Best12.).
- Month = Month(Date), format=Numeric(Best12.).
- Year = Year(Date), format=Numeric(Best12.).
In addition, make a Custom category on the variable DayOfWeek, with the name ''Day of the week''. Custom category could be found in New data item under the Data pane. Define it like this:
The last new variable is made by duplicating the Date variable, and changing the name to Month and the format to be MMMYYYY.
Step 2: The page in the canvas is build by the following steps:
- Drag in Button bar beneath Controls under Objects. Add the variable Year as the category role, and remember to put on Automatic actions on all objects found in the right pane without breadcrumbs. Then go to Filter in the right pane, and choose to filter the variable Year. Then select the wanted years to be showed, for instant the years between 2015-2024. After that, go to options and choose custom title and write a suitable text as ''Select Year:''. Double click the text in the button bar, then select desired color and size.
- Drag in a Standard container beneath Containers from Objects.
- Drag in a Crosstab from Objects into the Standard container and select the following:
- Columns = Day of the week
- Rows = Month, WeekNB
- Measures = Highest Stock
Step 3: To highlight the different stock prices with different colors, then it's possible to put on some rules on the Crosstab. Rules could be found in the right pane. Define for example the following rules:
Step 4: Go to Options in the right pane, then put on a white color to the Measure heading. Also, choose grey on the row heading text, column heading and column text.
Step 5: It could be smart to upload a Image on the left side of the button bar. The Image is to be found beneath Content under Objects. While standing on the Image, go to Options in the right pane and select custom text. Click the default text that appears, and write the text saying: ''What does the colors in the date calendar mean?''. Then make a new page where you drag Text from Objects to the canvas, and write an explanation of the rules used on the Crosstab. After that, click the three dots on top of the new page and choose page type=Hidden and rename=POP UP. Go back to the picture, right click and add link to the hidden page. The pop-up window could be looking like this:
Step 6: To get the Crosstab to just show the first six months, go to Filter in the right pane and select filtring on the variable Month. Then choose the first six months of first half year (1,2,3,4,5,6).
Step 7: Drag in Text beneath Content under Objects, and place it above the Standard container with the Crosstab. Then double click before writing: "Highest stock price on Nasdaq". Adapt the text with desired colors and size.
Step 8: The results could look like the following (maybe not the same colors):
Date Calendar for one month a year
Instead of showing all the months in the first half year, then you might just want to show one month at a time.
Step 1: Hopefully, all the steps above is done. If not, then do them. Else, dublicate the page by right clicking the three dots beside the name. When standing on the Crosstab, then go to Filter in the right pane to select all months instead of the six first month of the year.
Step2: Go to Objects, drag the drop-down-list beneath Controls to the right side of the button bar. Select Month as the category from Roles on the right pane. Go to options to put on a custom title saying for instant ''Select Month:''. Remember to put on Automatic actions on all objects by clicking Actions in the right pane and deselect breadcrumbs. Also, click the text in the drop-down-list to choose the desired color and size.
Step 3: Select a desired year and a month. The result could look like this:
Date Calendar for all the months during a year
Sometimes, it could be beneficial to make a Date Calendar for all the months during a year. Then you need to do the following steps:
Step 1: Hopefully, all the steps is done from the first example that shows Date Calendar for the first half year. If not, then do all of them.
Step 2: Dublicate the page by right clicking the three dots beside the name.
Step 3: Be sure to be standing on the Crosstab, then go to Filter in the right pane and select just the first month.
Step 4: Dublicate the Crosstab with first month. Then it will pop-up beneath, so drag it beside the first month.
Step 5: Dublicate once more, and it will appear beside the two Crosstab.
Step 6: Go to Outline on the left side, then click the Standard container on the page. This will highlight the Standard container so you could right click the three dots and select duplicate so you get a copy beneath.
Step 7: Repeat Step 6 two times more, which will entail four identical Standard containers on that page.
Step 8: The last step is to go through each of the 12 monthly Date Calendars and selecting the correct month from the Filter in the right pane.
Step 9: And vola! After selecting the year 2023, the results could look as following:
Have a great summer!
