To finish off this step I parsed out the year from the contract date using a simple formula tool.

Finally, I used the multi-row formula tool to add an additional quarter number for each new contract date for each client.Īs you may be aware, there is no such equivalent of the multi-row formula tool in Tableau Prep so I knew this would require a slightly different technique, more on that later. I then sorted by client ID and contract date ascending. First I used the Date Time tool to convert the contract dates from a string to a date-time value. The next stage was to generate the quarters for each client. This is easily achieved using the text to columns tool to split on the | delimiter, I followed this up with the data cleansing and select tools to remove unwanted whitespace and remove the additional field. The first step I took was to parse out the client ID, contract date and order volume, as they were all contained within one field. This was sorted to show the quarters with the highest profit margins. The output required was a table which contained the costs, revenue and profit margin for each quarter. For each year there is a quarterly premium, a per-unit fee for customers and a per-unit production cost. One held information about the contracts of clients, this included a client ID, contract date and the order volume for that month.

Quarters are defined by the length of the relationship with your company.įor example, if customer A’s first contract started, your company defines Q1 with this client as, Q2 as, Q5 as, Q9 as etc’.Ī link to the challenge can be found here. Quarters aren’t defined in the traditional sense though. ‘ Your task is to create a workflow that determines the quarter where your company has the highest profit margin with its clients. So after completing the first challenge of 2020, I figured I might as well bring the two together and try to complete the challenge in Prep Builder. Two of my goals for this year are to make this comparison blog a more regular feature and to complete more Alteryx weekly challenges.

Tableau prep workflow