Contract Fill %

ChrisGaffan
ChrisGaffan Member
in Magic ETL

I have a card that i'm intending to use to track fill % of contracts. The card has columns for Commodity, Week#, Contract Weight, Actual Received Weight and then a Beast Mode Column to calculate the % of the contract filled.

I'm feeding the card through an ETL. There are two input datasets in the ETL. One consists of actual received weight and the corresponding Commodity, Week #, Vendor Date. There are multiple "Received Date" rows in this data set.

The second dataset is an excel sheet with the contract weights per week (Most of which are exactly the same each week) and again corresponding Commodity, Week # .etc .etc.

My issue is that i'm missing a step in the ETL that is making the "Contract Weight" per week to double up on itself based on how many lines of "Received Date" data there is from the first dataset.

An example below:

image.png

The data in the "Pounds" column should be the same # each week. Instead, it is doubling each time there is a "Total Weight in lb's" for that row. I understand the logic behind that, I just do not understand how to get around it within the ETL.

Is there a way to sum the entire week worth of Total Weight Data so that each week there is only one data point?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You could use the start date of the week as your date range so that you have a single week value like your week number but is an actual date. You can use a formula tile and a formula like:

    `date` - INTERVAL (DAYOFWEEK(`date`)+1) DAY

    Then you can group your data on this date to either take the min, max, sum or whatever aggreagation you need for the week.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • marcel_luthi
    marcel_luthi Coach

    @ChrisGaffan without knowing how the sources look like, I'm making a guess on what might work for you. In your ETL before the JOIN tile, use a GROUP BY tile, using Commodity, Week # (adjust to use all the columns you're using on your Join key) and use a formula of SUM on the Weight this will make it so now you have a 1 to 1 relationship top for each key, and make sure you're using a FULL OUTER join, just to cover scenarios in which you might have received but had no contract or had a contract but failed to receive.

    Optional: after the join you can use an ALTER COLUMNS tile to change how NULLs are to be handled so you can fill those with 0's which might be desired when performing calculations.

    This first alternative works as far as you don't need to break it / drill down to a higher level of granularity. Another option is not to use a SUM for the Contract Weight but a beast mode instead, that tells it to average at the right level ( Commodity, Week # ) and SUM it for anything above it. Something like:

    SUM(AVG(`Contract Weight`) FIXED (BY `Commodity`,`Week #`))

    Hope this helps.

  • ChrisGaffan
    ChrisGaffan Member
    edited December 2023

    @marcel_luthi The 'Receive Date' column is breaking the functionality of the Group By. I have the 'Receive Date' data converting to "Week #" within the ETL. However, I can not leave the 'Receive Date' column out of the final dataset due to Domo's need to have a range field for the 'Date Range'.

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You could use the start date of the week as your date range so that you have a single week value like your week number but is an actual date. You can use a formula tile and a formula like:

    `date` - INTERVAL (DAYOFWEEK(`date`)+1) DAY

    Then you can group your data on this date to either take the min, max, sum or whatever aggreagation you need for the week.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories