This question is Part 7 of our free series of questions along with step by step video and Text based solutions for Tableau Certification Exam Question Dumps including Tableau Desktop Specialist Certification Exam Dumps and Tableau Desktop Certified Associate Exam Dumps. You will see similar questions in both above mentioned certification exams. In Specialist Exam you can expect 8 to 10 scenario based questions where as in Associate exam, it’s almost double. We strongly recommend to have a walk through such material before your real exam. All data sources used in these questions are available in Data Source Page at our website Menu.
Use SPCL_Sample-Superstore-Subset Excel File and using Orders sheets,Find the moving average value in 2013 for Product Sub Category ‘Tables’. Considering previous 3 years for calculating moving average formula
This question validates your knowledge about the concept of Moving Average. In question, it is asked to find the moving average for Product Sub Category ‘Tables’ for the year 2013. Important point to note in this question is to calculate the average value based on the previous 3 years whereas by default Tableau calculates the moving average value based on previous 2 years and including the current year value. Detailed explanation for reaching the required value is given in the Text based solution below.
Note: if you want to practice this question on your PC, you can download the data source from data sources page from our website menu.
Moving Average is a very useful feature. In order to learn how Moving Average works in Quick Table calculations, you can continue your learning journey and can read the following article from Tableau knowledge base.
It will open a new blank workbook like following
Click on Connect to data
From following screen click the Excel data source option.
It will ask the location for the data source available on the system, select the correct data source file and load it, Once you select the excel data source file from your local PC, it will be loaded in the Tableau and will look like as following
Select Orders Sheet and drop in the data pane
Its data will be visible and will look like this
Click on the sheet icon to create a new sheet as shown below
New Sheet will open like this
Click the Order Date and drag it to columns shelf
Sheet will look like this
Now select the product sub category and drop to filters pane
It will show the list of all product sub categories as shown below
Now select the product sub category ‘Tables’ as it is mentioned in the question, once it is selected click OK as shown below
Sheet will look like this
Now select the Sales measure and drag on the Text as shown below
It will reflect the sales figures for every year for product subcategory Tables
In the question, it is asked to find the moving average, Right click the Sales Measure in the Marks card and select the moving average option as shown in picture below
It will show a small arrow on the Sales measure in the Marks card and values of moving average will be available in data sheet as show.
Let us now check the formula for moving average through which it has calculated the value of 289,385 for year 2013.
In order to see the formula, right click the sales measure and select the option as shown in screen shot below
Clicking the Edit will open the following dialog box which shows the logic for calculation of moving average.
It can be clearly seen that moving average is being calculated across the table and it is considering previous 2 years to calculate the moving average
Following further properties can be seen by clicking on the small arrow as shown in the below navigation.
Let us understand the logic how it is calculating the value 289,385 for year in 2013.
We know that sales for year 2011, 2012 and 2013 are respectively 226086, 346218and 295853.
If we see the above default setting of moving average, Current Value check box is checked it means that while calculating the average value, it will consider previous two years along with current years values.
But in the question, it is asked to calculate the moving average based on the previous 3 years so we have to edit the formula for calculating the moving average as shown below
As soon as you change the value from 2 to 3, immediately the value of moving average for year 2013 changes to 265480 as can be seen in the screen shot below
Let us see how it has been calculated, It has now included the value of year 2010 as well to calculate the moving average and hence total is divided by 4 in order to find the moving average value as shown below
So the required answer is 265,481.
If you have any question, feel free to mail us or send whats app message.
We are just one message away from you, We answer every query, Feel free to get in touch.