Magic ETL Help

damen

Hi,

I am going through the Data specialist practical test and am running into troubles.

Here are my instructions:

Up to this point, this is what I have done:

Because I am not an admin on my company network, for those familiar to this test, I was unable to complete the join on the workbench due to accessibility issues. However, I was able to join them in ETL given my functionality.

I have come to the point where I have selected all the columns I need but am not sure how to calculate the average time between orders BY CUSTOMER. It makes sense to use the 'date operations' tile and ask for a subtraction from transaction dates but I am unsure (and it doesn't give me the correct output) how to do that same function and include the customer ID, which it seems to be asking.

Any help would be awesome. Pictures explaining would be much appreciated.

Thanks

RobSomers

@damen Since it's a practical test, I won't give the answer, but I will give a hint. Look into the Rand & Window tile.

https://domohelp.domo.com/hc/en-us/articles/360042922814-Old-Magic-ETL-Tiles-Rank-and-Window

damen

@RobSomers Ok maybe another hint?

I was able to essentially able to group or sort on customer ID by using the rank function but am still lost on how I could calculate the difference between rthe rows. Is there a way to create a new column where I would take the day of the year (ex: Jan 1 would be 1 and december 31 is 365) add them up based on customer rank and divide by the number of instances? for example. customer 1 had their first order jan 14 (0), feb 16 (roughly 30) and june 26 (roughly 180) add those up (30+180=210) and take 210/3 =70 --- which in this case would be about what their average days between order is?

Long and drawn out apologies but I'm more new to the data stuff than I am the business intelligence and visualization work

RobSomers

@damen In the Rank & Window tile, use the LAG function, ordering by transaction date and partitioning by customer. The LAG function looks at the nth previous row. So if you did a lag of 1, it would look at the previous row and the column would now provide you with the previous date, and you can do a formula to calculate the difference between the dates.

damen

@RobSomers That makes sense. And which tile do I use for the 'A screenshot of which customer has the longest average time between orders and what is the average number of days?' part of it? I keep messing with the calculator and rank and window and cant seem to get it

RobSomers

@damen After adding the column that calculates the days between orders, you can do a Group By tile to group the customers together and average the days between orders.

damen

@RobSomers Thanks man. I appreciate the walk through and your patience