I have covered similar problem using Power BI before, you can find it here. Now I would like to show you how you can tackle this different granularity problem with Alteryx. Alteryx is an powerful ETL tool but it also does other amazing things such as spatial analysis and predictive analysis. I will cover these in future post. so what I am showing you today is how you can use Alteryx to prepare your data and spit out a tde file for tableau to visualise your data. (note that Alteryx has basic reporting capability as well but it’s horrible compared to that of Tableau). Some sample data as below: daily level transaction data and quarterly sales quota data.
If you load these data into tableau it’s not going to be able to consume it easily. You can ask your IT to break your quarterly sales quota data down to day level and then use tableau to visualise it. Since we are in the era of self service BI, let’s do it all by ourselves with Alteryx. I have massaged the above data set to be in below format so that both data set are now at quarterly level. Now I can easily join them and find out which sales person hits target for each quarter.
I designed below workflow in Alteryx to do the job. (I could have used SQL script to get desired data for my analysis, but I want to show you how you can do it with Alteryx)
Step 1: drag an input tool to get my sales data from my local instance of SQL server. You can see the incoming data and data type as below. Note that SalesAmount is string type.
Step 2: drag an auto field tool to change the data type. You can see SalesAmount is now Double type.
Step 3: drag a datatime tool to parse the OrderDate to remove the time component.
Step 4: Drag a text to column tool to split the DateTime_Out field.
Step 5: drag a formula tool to get my Year-Quarter column.
Step 6: Drag a select tool to remove unwanted column.
Step 7: Drag a summarise tool to aggregate my sales data to quarter level for each sales person. I have put step 1 to 7 in a container so it’s easier for other people to follow my logic flow.
Step 8 to 11: Manipulated the sales quota data set coming from SQL server to be in the same structure as my sales data. You can see the end result as below.
Step 12: drag a join tool to combine the 2 data sets.
Step 13: I have added 3 browse tools to check my output data set from step 12. This tool is often used to see your data set after transformation performed to ensure you get intended result. I don’t expect to see any data in the upper and bottom browse tool as all my employees should have sales quota defined in my data set. If I had employees without associated sales quota or vice versa, then I would have seen data coming out of those 2 browse tools.
Step 14: drag a output tool to generate a tableau tde file, so you can visualise your final output in Tableau. My mock-up data set for sales quota is too high as no sales person had reached it. Anyway, you get the idea how you can use Alteryx and Tableau together for your analysis.