Options
Member

Magic ETL Activity

Using the DataSet in Domo you created using Workbench in the previous activity,

1. Use Magic ETL to parse the currency code from the "Transaction Amount" column and separate the amount and the currency code into separate columns.

Submit: A screenshot of the output of the data showing the different columns created.

2. Calculate the average time between orders. For each record in the DataSet, add a field showing the number of days since the previous order for that customer.

Submit: A screenshot of which customer has the longest average time between orders and what is the average number of days?

so this is my practical quiz, I have no experience in this but i got a job as business intelligence and I have to finish this course, I have no problem with questions number 1 it is questions number I am very confused about.

this is how I do so far, I use the rank window to create the rank and another to Calculate the average time between orders. honestly, I don't know if I'm doing this correctly, can someone please let me know if what I did is correct? please? also how can I show which customer has the longest average time between orders and what is the average number of days?

• Coach
Options

@fadhlirrahman The next step would be to save and run the dataset and then create a card in which you can use a table card to show the longest average time between orders and the average number of days by using the DATE_DIFF() function. Let me know if you get stuck!

• Member
Options

i got questions for number 2 please anyone im so frustrate i search everywhere i cant understand

• Coach
Options

To see the time between orders for a customer you can use the rank and window tile with a lag function. Order by order date and partition on customer ID. Let me know if you are still stuck!

• Contributor
Options

@fadhlirrahman , For #2, I would investigate the rank & window tile. You must classify the 1,2,3,4,etc. order ranking for each customer ID. From there you can take the difference of days using date operations between the orders to then avg. I hesitate explicitly laying the answer out through snapshots as this is for certification, but this will definitely get you started in the right direction. :)

• Member
Options

Hi it is solved, thank you

• Coach
Options

• Member
Options

i have managed to get the average time, it looked like this. but now im wondering on how to get A screenshot of which customer has the longest average time between orders and what is the average number of days?

do i use group by?

• Coach