Finding Average Depending on Warehouse

user03552
user03552 Member
in Magic ETL

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!

 

 

Best Answer

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    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.

     


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

Answers

  • Property_Ninja
    Property_Ninja Contributor

    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.


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

    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
    Property_Ninja Contributor
    Answer ✓

    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.

     


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

Categories