Sign in or start a free trial to avail of this feature.
2. Analytical App – Initial Data Preparation
Learn how to build your first Analytical App. In this first lesson, we prepare a workflow suitable for conversion to an Analytical App.
Preparing data for app creation
- Users should create an underlying framework for the desired app
- Designing this framework generally requires thinking ahead about how you want the app to appear, and what tools should be interactive
- The Table tool organizes the select data in a tabular format
- The Render tool converts report tools to the user’s desired output
As mentioned in the previous lesson, Alteryx apps are extremely dynamic, and can be used to simplify your workflows into tidy packages. These packages can then be used to deliver the right information in a format that's easy for anyone to understand. The key to the dynamism of an app is how it enables you to toggle variables within a workflow. Let's investigate this further. The first step in creating an analytical app is to map out a full workflow.
Once you have a basic workflow addressing your data needs at a high level, you can introduce appropriate interface tools. These have the effect of customizing key parts of the workflow, creating an interactive environment. In this example the company serves a global customer base, and sales have been recorded in various currencies. We would like to create an app that generates a simple sales report with all values in Euro for a specific date range. We won't be creating the app in this lesson, instead we'll create the framework that will eventually become our app. To accomplish this goal, we'll follow six steps. First we'll ensure our data is correctly formatted and combine our data sets. We'll then set a date range for the sales report. For the third step, we'll convert all sales figures to Euro, and aggregate sales data by product. Next we'll sort the data and rename the headers. We'll then filter the data set for individual products. Finally we'll specify the report output. We'll start by combining the data sets. As we can see we have two different data sets, one containing daily sales figures, and the other containing daily foreign exchange rates. We'll convert dates in the Daily Sales data set to ISO format with the Date Time tool.
The tool automatically picks up most of the required input and settings, but we'll need to set the matching format as dd-Mon-yy.
Next we'll add a Select Tool, convert both Sales and Quantity fields to fixed decimal size 12.2, deselect the Date field, and rename the Date Time Outfield as Date.
We'll do the same for the FX rates data set. Again we'll connect the Date Time tool, and select the dd-Mon-yy format.
We'll then connect the Select Tool, deselect the Date field, and rename the Date Time Outfield as Date.
We'll also convert the FX rate field to a fixed decimal type, however this time we'll choose a size of 19.6 as we'd like a precision of six decimal points for the exchange rates. We'll now connect the Join tool to the FX rate in daily sales data sets to merge the information. In the configuration window, we'll match these data sets on currency type and date.
We'll work with the combined data set for the rest of the workflow. Moving on to step two, we need to be able to specify a timeframe so we'll add a Date Filter tool. For this example we'll target data between May 1st, 2016 and May 31st, 2016.
For the third step we'll need to convert sales figures to Euro, and aggregate our data by product. We'll add a Formula tool to convert all sales to the home currency. We'll name this field Sales Home Currency, and simply enter sales divided by the FX rate, and change the data type to fixed decimal, size 12.2.
We'll also add a second formula canvas called Total which will contain a simple constant, Total, in quotes. This will be useful when we aggregate our data. We'll run the workflow and see that we now have a new column detailing our sales in the home currency. At this point we'll need to aggregate our data so it's suitable for our management report. We'll accomplish this by connecting a Cross Tab tool. As a reminder, the Cross Tab tool pivots the orientation of the selected data. For a more in-depth review of this tool, please refer to the Record ID and Cross Tab lesson in the Getting Started in Alteryx course. We'll now group the data by product. We'll specify the new column header as the Total field we recently created, and the values as the Sales Home Currency field. We'll then choose to sum the consolidated fields, and also calculate the percentage of total using the Percent Column aggregation. We'll run the workflow again, and see that we now have a list of products together with the total sales in Euros, as well as product sales as a percentage of the total sales over the time period. For the fourth step, we'll clean up the data set by sorting data and renaming headers. In order to create a more appropriate layout for presentation, we'll connect a Sort tool, and list our products in descending order of sales.
We'll then connect another Select tool to give our columns more appropriate names. We'll rename the Sum_Total Column as Product Revenue, and the XCol_Total Column as Percentage of Total Sales. Moving on to step five, we'll create a mechanism to filter for individual products. To that end we'll connect a Filter tool to the workflow. For now we'll set the tool to filter for the product Fabaceae. For the final step, we need to specify our output report. We'll navigate to the Reporting tab on the Tools palette, and add a basic Table tool to the workflow. In the configuration window, we'll specify Product, Total Sales, and Percent Total Sales under Per Column Configuration. This will produce a neat table which will form the basis of our report. Finally we need to specify the output mechanism. To do this we'll add a Render tool to our workflow, specifying a PDF document as our preferred format. We've now completed our basic workflow. Before moving on, let's quickly recap this lesson. First we reformatted our data and combined our data sets.
We then set a date range for the sales report. Next we converted all the sales figures to Euro and aggregated sales data by product. In the fourth step we sorted our data by sales in descending order, and renamed headers to ensure clarity. In step five we added a Filter tool to filter the data set for individual products. Finally we connect to the basic Table and a Render tool to output the PDF report. In the next lesson, we'll deploy interface tools to create the analytical app.