Finding Average Depending on Warehouse

user03552

Hey Everyone,

 

I am trying to make a card that finds the average of each warehouse depending on how quickly they unload a container. Then I will be giving them a "Grade" (A,B, C, D, F) depending on there average for the Year, Quarter, Month, and week. The thing that I am struggling to figure out is how can I have domo do an average for each of the warehouses M, G, W. 

 

The attatched image shows the "ship date minus Release date" (which is a calculated field) I want this to corelate with the "warehouse" column so that I can find the average per warehouse. 

 

Let me know if you need any clarifying details.

 

And thank you for your help!

 

 

Property_Ninja

So I am assuming by your comment that you want to assign a letter grade to the average?

 

So you could have this in a table format by writing another beastmode with a case statement for the letter grades. Something like ... 

case when avg(days) < 3 then A
when avg(days) < 5 then B
etc ... END

Then your table would have Warehouse, Average Score, and Letter Grade. 

 

You could also do this in either Magic ETL or a MySQL DataFlow. Basically creating an aggregated table like below. This will allow you to show your average days and the corresponding grade in a table structure.

drop table if exists warehouseaverages;
create table warehouseaverages as
select warehouse
,avg(days) as averagedays
from warehousedays
group by warehouse;

-- then create another table off of that last table

drop table if exists warehousegrades;
create table warehousegrades as
select warehouse
,averagedays
,case when averagedays < 3 then A
when averagedays < 5 then B
etc ... END AS warehouse grade
from warehouseaverages

Brian

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

 

Property_Ninja

You can create a beastmode in a card that is simply AVG(`ship date minus release date`) then use the Warehouse Name as either the series or the x axis. This will give you the average of a given warehouse depending on the date range you choose.

 

Thanks,

 

Brian

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user03552

This would mean I would have to use a Bar Graph which would then not allow me to have a letter grade showing on the card. Or is there a way to still incorperate the letter grade it would recieve?

Property_Ninja

So I am assuming by your comment that you want to assign a letter grade to the average?

 

So you could have this in a table format by writing another beastmode with a case statement for the letter grades. Something like ... 

case when avg(days) < 3 then A
when avg(days) < 5 then B
etc ... END

Then your table would have Warehouse, Average Score, and Letter Grade. 

 

You could also do this in either Magic ETL or a MySQL DataFlow. Basically creating an aggregated table like below. This will allow you to show your average days and the corresponding grade in a table structure.

drop table if exists warehouseaverages;
create table warehouseaverages as
select warehouse
,avg(days) as averagedays
from warehousedays
group by warehouse;

-- then create another table off of that last table

drop table if exists warehousegrades;
create table warehousegrades as
select warehouse
,averagedays
,case when averagedays < 3 then A
when averagedays < 5 then B
etc ... END AS warehouse grade
from warehouseaverages

Brian

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.