Dynamic Weekly goals

user04612

We have 2 tables:

OrderDetail - which stores order info like date, sku, quantity, price, ect

SalesGoals - DateWeekStart, DateWeekEnd, SkuQuantityGoal, etc

Is there a way we can do a visualization that dynamically shows the weekly sales against weekly Goals?

I am new to this, so I am not sure the best way to set up the sql in workbench, and then create the visualization in a card.

thanks for any help anyone can give on this or direct me to the best place to find the answers

Brian

MarkSnodgrass

You would need to combine the data in Magic ETL so that it is one dataset. You would have two input datasets: order detail and sales goals. I would use a formula tile on your order detail to get the first day of the week for each order. You can use the following formula to do this (replace dt with your actual order date field:

DATE_SUB(`dt`, INTERVAL (DAYOFWEEK(`dt`) - 1) DAY)

You would then use a group by tile to group the orders by the first day of the week. You can then use a join tile and do a left join with your goals on the left and join on the first day of the week field and likely your sku.

This should get the data in the shape that you want so that you can build a card off of it. I have a few videos (link below) that may help you if you are unfamiliar with any of those tiles.

MarkSnodgrass

All ETLs have settings that allow you different ways to control when you want your ETL to run. When editing the ETL, you can click on the icon that is just to the right of the zoom controls (it looks like 3 lines with dots on them). I would suggest having it run whenever either dataset is updated. This will ensure you output dataset is always up to date.

MarkSnodgrass

You would need to combine the data in Magic ETL so that it is one dataset. You would have two input datasets: order detail and sales goals. I would use a formula tile on your order detail to get the first day of the week for each order. You can use the following formula to do this (replace dt with your actual order date field:

DATE_SUB(`dt`, INTERVAL (DAYOFWEEK(`dt`) - 1) DAY)

You would then use a group by tile to group the orders by the first day of the week. You can then use a join tile and do a left join with your goals on the left and join on the first day of the week field and likely your sku.

This should get the data in the shape that you want so that you can build a card off of it. I have a few videos (link below) that may help you if you are unfamiliar with any of those tiles.

user04612

Thanks so much Mark!

I think I have things working fairly well….Attached are some images of my setup and card.

Questions:

• I noticed I has to 'Run' the dataset after I created the ETL - Will this dynamically update based on my schedule in workbench?
• If my imported excel file needs to be updated, do I need to delete the current one and re-add the input Dataset?.. and if so, will I need to recreate the steps from the join on? or can I update the Sheet right in Domo?.. what's the best way?

Really appreciate your help on this.

Brian

ETL.gif

card.gif

MarkSnodgrass

All ETLs have settings that allow you different ways to control when you want your ETL to run. When editing the ETL, you can click on the icon that is just to the right of the zoom controls (it looks like 3 lines with dots on them). I would suggest having it run whenever either dataset is updated. This will ensure you output dataset is always up to date.

user04612

thanks again Mark!