Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
javier_rios
Alteryx
Alteryx

Let’s use Designer Cloud & Reporting to solve the weekly challenge 363.

 

According to the Harvard School of Public Health, tea and coffee are the two most consumed beverages on the planet after water. They are brimming with antioxidants and other biologically active substances that may benefit your health.  

 

For this week's challenge, we will use a dataset containing information about the sales of coffee and tea in the USA for a 6-month period.  

 

Here are the questions we want to answer using the data provided:

  • Which state is the biggest consumer of regular coffee?
  • Which state is the biggest consumer of decaf coffee?
  • What type of coffee drink is the most popular?
  • Which state is the biggest consumer of regular tea?
  • Which state is the biggest consumer of decaf tea?
  • What type of tea drink is the most popular?

 

Sample data:

javier_rios_0-1680278421252.png

 

Background

 

What is Designer Cloud?

 

javier_rios_1-1680278421269.png

 

Designer Cloud is the only open and interactive data engineering cloud platform to collaboratively profile, prepare, and pipeline data for analytics and machine learning. Designer Cloud provides an interactive, visual user experience that uses AI/ML-based suggestions to guide you and your data teams through the exploration and transformation of any dataset.

 

What is Reporting?

 

javier_rios_2-1680278421271.jpeg

 

Reporting is an intuitive report builder that helps data workers frictionlessly share data insights across all levels of an enterprise. Alteryx Analytics Cloud Platform makes it easy to create personalized data visualizations, export and share reports with anyone, and consume data that helps make intelligent decisions.

 

Solution Approach

 

We are going to use the following products:

 

  • Designer Cloud
    • Data preparation and manipulation
  • Reporting
    • Results visualization

 

Data Preparation and Manipulation

 

Download the CSV file and load it into your solution (see this post for more information about reading and writing CSV files). Let’s load the data: Drag the Input Data tool from the Tool panel and drop it into the canvas, then proceed to select the Coffee_Tea_Sales.csv file.

 

image005_edited.png

 

Once the data is loaded, we need to re-configure the data types (By default, all the field types are strings when importing data from a CSV file). Changing the type of variable (for example, from string to float) can be done using the Select tool. Select the desired variables from the Select tool configuration panel to the right and change from String to Float type to perform SUMs and AGGREGATIONS on them.

 

javier_rios_4-1680278421570.png

 

We can review the changes by looking at the Metadata View icon and comparing the Input to the Output of the Select tool.

 

image009_edited.png

 

image010_edited.png

 

Now that the variables have the right type let’s aggregate the data to answer the questions.

 

Designer Cloud lets you perform arithmetic functions and aggregate them at different levels. We are going to use the Summarize tool to calculate the total sales for Coffee/Tea and group it by State, Product Line (tea/coffee), and Type (Decaf/Regular). Doing so, we are going to answer:

  • Which state is the biggest consumer of regular coffee?
  • Which state is the biggest consumer of decaf coffee?
  • Which state is the biggest consumer of regular tea?
  • Which state is the biggest consumer of decaf tea?

 

Let’s drag and drop the Summarize tool to the canvas and connect it to the Select tool. Once connected, we need to configure the Summarize tool. We need to Sum Sales and Aggregate the results to State, Product Line, and Type. From the Configuration Panel, we select the actions for each variable by clicking on the “+” sign.

 

image012_edited.png

 

All the actions added will show in the “Actions” area in the configuration tool. Notice that we do not need to run the workflow to see the results of our aggregations – this is a great feature that Designer Cloud offers to the users – BIG TIME SAVER when building workflows. We can also re-arrange or delete the order of the actions.

 

image016_edited.png

 

This far, we have calculated the total sales (Sum_sales) for each Product Line, Type, and State. The next step is to sort the States, Product Line, and Type based on Total Sales in descending order and assign a rank to the results. We can perform these actions using the Tile tool. Once the configuration of the Tile tool is done, we can see that there are two new columns added (Tile_Num and Tile_SequenceNum)

 

image019_edited.png

 

Next, we will remove, rename, and filter the results – to make them easier to read. To rename and un-select fields, we use Select. We will rename the Tile_SequenceNum to Total_Sales_Ranking and un-select the Tile_Num (unchecking it from the Select configuration tool panel). To filter just the top state (Total_Sales_Ranking = 1), we will use the Filter tool. The results will be in the “T” anchor (where the filter condition holds true)

 

image021_edited.png

 

We are going to export the partial results in a CSV file format on our local computer. (Read this post to learn more about how to Write a CSV file).

 

We still need to answer the following questions:

  • What type of coffee drink is the most popular?
  • What type of tea drink is the most popular?

 

To answer the last two questions, we need to calculate the total Sales for each product line and rank the results based on the total Sales.

 

We will start with the Summary tool and configure it to calculate the Sales Sum for each Product Line at the Product Type level.

 

image025_edited.png

 

To sort and rank the results, we will use a Tile tool and to rename and remove fields we will use the Select tool as follow:

 

image028_edited.png

 

We can output the results similarly as above; we are creating two CSV files that are going to use when creating the report. And to make this workflow clear to any user, we will add containers and comments to the process.

 

javier_rios_13-1680278422818.png

 

Results Visualization

 

You can access Reporting from the app switcher within the Platform. Once you are in the application, start by choosing from your library of imported datasets to begin building reports.  

 

image032_edited.png

 

javier_rios_15-1680278423153.png

 

Let’s bring the results from the workflow we build. We need to import the CSV files from our computer to the Cloud and make them available for the Reporting Tool. Once the two outputs are available, we need to connect the Reporting tool to them.

 

image035_edited.png

 

We have created two different outputs using Designer Cloud, and now we are going to use them to create two reports.

 

Report 1

Report 2

Which state is the biggest consumer of regular coffee?

Which state is the biggest consumer of decaf coffee?

Which state is the biggest consumer of regular tea?

Which state is the biggest consumer of decaf tea?

What type of coffee drink is the most popular?

What type of tea drink is the most popular?

 

 

To build reports, we will create the elements first and then use them into the canvas. Watch this video for more information.

 

Create and use elements

 

To create the Table element, we need to click on “+ Add” and then on Edit. Remember that the element will be populated with the data loaded.

 

image039_edited.png

 

image041_edited.png

 

Let’s rename the table, and de-select the fields we don’t need on the report.

 

javier_rios_19-1680278423649.png

 

Let’s create rules to customize the table. Column Rules allows you to create a rule for a specific column. In our example, we are going to search for the word “Coffee” in the product_line column, and once we find a cell with that content, we are going to change the background on that cell – the same for Tea.

 

javier_rios_20-1680278423822.png

 

javier_rios_21-1680278423966.png

 

Similarly, let’s add a Text element and edit it.

 

javier_rios_22-1680278424058.png

 

Once all the elements are created, we proceed to use them in the Report’s canvas area.

 

javier_rios_23-1680278424190.png

 

To edit the elements on the canvas, hover over it and click on the pen icon.

 

image048_edited.png

 

Editing the Layout

 

image050_edited.png

 

We add margins to the report to improve readability, and we can also change the report’s orientation.

 

Exporting the report

 

image052_edited.png

 

Finally, the results are in a PDF format – ready to be shared :)

 

javier_rios_27-1680278424793.png

 

javier_rios_28-1680278424885.png

Comments
AlteryxMarco
Alteryx
Alteryx

Incredible @javier_rios please keep more like this coming!