My previous blog highlighted some best practices to gain immediate value from all the capabilities that AtScale offers when using Tableau as your BI tool of choice. One of those best practices is to use AtScale-created date dimensions to improve query performance, which is particularly helpful when using date dimensions as filters.
But what about using date dimensions to compare period over period? Is there a perfect way to implement custom date comparisons using AtScale and Tableau? Can we create an analysis experience for the business user that is both time intelligent and visually appealing? Definitely! This article describes the most flexible implementation for creating time intelligent analysis using AtScale and Tableau.
The best way to allow for flexible date comparisons would be to create a calculated field that contains the required date comparisons, and the supporting parameters needed to run the calculations.
Step 1: Create Parameters required for date calculations.
1. Create a parameter called “Analysis Type”, with values of the types of Analysis comparisons that are required. In this case, we are looking at Rolling Period, Year over Year, Month over Month and YTD (see screenshot)
2. Create a parameter called “Custom Date”, which allows the user to input any date they want, and then can be used in other parameters and calculations (see screenshot)
3. Create a parameter called “Date Chooser”, which allows the user to specify the date to be used in the comparisons. In this example, we are using 2 values: Today and Custom Date.
4. Create a parameter called “Period Length”, which allows the user to how many periods are to be evaluated. In this example, we are using 12.
5. Create a parameter called “Period Type”, which allows the user to specify the date part to use in their comparisons. In the example below, we are using Day, week, month, quarter and year.
6. For all the newly created parameters, right click and select “show parameter control” so they can all be seen on the view.
Step 2: Create Calculated Fields that use new parameters
1. Create a calculated field called “DateComparisonToUse” , which calls the “Date Chooser” Parameter.
2. Create a calculated field called “DateforIndex”, which calls the “Period Type” parameter to be used in an Index calculation.
Step 3: Create Calculated Field called “Analysis Calculations”, which uses the new parameters and calculated fields to create the different date comparisons the user wants to see
|
CASE [Analysis Type] WHEN 'YoY' THEN IF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >=0 AND DATEDIFF([Period Type],[Date],[DateComparisonToUse])< [Period Length] THEN 0 ELSEIF DATEDIFF([Period Type],[Date],DATEADD('year',-1,[DateComparisonToUse])) >=0 AND DATEDIFF([Period Type],[Date],DATEADD('year',-1,[DateComparisonToUse])) < [Period Length] THEN -1 END WHEN 'MoM' THEN IF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >=0 AND DATEDIFF([Period Type],[Date],[DateComparisonToUse])< [Period Length] THEN 0 ELSEIF DATEDIFF([Period Type],[Date],[DateComparisonToUse]) >= [Period Length] AND DATEDIFF([Period Type],[Date],[DateComparisonToUse]) <2*[Period Length] THEN -1 END WHEN 'RP' THEN WHEN 'YTD' THEN IF DATEPART('dayofyear',[Date]) <= DATEPART('dayofyear',[DateComparisonToUse]) THEN // The case statement determines if the date is in the same year or the previous year. Any other years are null. CASE YEAR([Date]) - YEAR([DateComparisonToUse]) WHEN 0 THEN 0 WHEN -1 THEN -1 END END END |
For ‘YoY’, ‘MoM’ and ‘RP’, the first statement sets the conditions for a date to be considered the “selected period”. It will check every date to the date we’ve chosen as our starting point. It must be based on whatever date we’ve chosen and go back a user chosen number of months, weeks, days etc. If the difference between these dates is >=0 and < our Period Length we consider it to be the 'selected period'. For ‘YTD’ we check every date in our view and see it would come before our selected date in a calendar year. If it does then we pass it to the case statement below if not we exit out and it is null. The case statement determines if the date is in the same year or the previous year. Any other years are null. |
Step 4 – Create the View
1. Set the Parameters to the screenshot values.
2. Place the “Analysis Calculations” field on the Rows. Right click and select “filter” and then de-select “Not Being Used”
3. Place the Sales TY Measure on the row next to the “Analysis Calculations” field.
4. Place the “DateforIndex” field on the “Details” shelf. Right click and select “exact date and continuous”. The Pill should be green.
5. In the Column shelf, type in “index()”. Then right click and select Table Calc - Compute Using DateforIndex. The index() calculation evaluates the values in the equation and reduces them to just whole numbers like “1, 2, 3…”
6. If you change the parameters to the following: Analysis Type = Year over Year and Period Type = Months, you will get the following result.
This view shows the Year span in but month slices of 12 and metrics reflex the month sum.
We hope the approach described in this article allows you to create insightful time-based reporting to get the most out of your AtScale and Tableau investments.
We invite you to learn more about AtScale today!