Calculating existing delays , Off delays and new delays on manufacturing data

ozarkram

 Hi All ,

I am new to Domo and I am trying to create a beast mode for the following type of data...

I have a manufacturing plant data on a daily basis and specific vehicle (denoted by sno) belonging to a specific category (mmodel) that can go on a delay due to some issues.

Now my Input tab has the snapshot of this data on a daily basis and denotes the vehicle (combination of sno and mmodel) that might be a new delay in comparison to the previous day (new delay implies that the vehicle combination dint exist on the immediate previous day to the day in comparison) ; existing delay - the same vehicle combination existed on the previous day or Off delay -the vehicle combination for the previous day doesn't exist today (or the day in consideration).

The numbers for the current day are calculated based on the vehicle entries on the current day and the immediate previous day.

The first day is the day of reference where all entries are just delays and nor off delay or new delay...The subsequent days entries are compared with the immediate previous day to get the delays ,new delays and off delay... The output tab 1 is a summary view by snapshot date and the output 2 tab is a view by master model and snapshot date.

Can you please help me here.

The earliest date in my data (ascending order) would be considered as the date of reference. So in this case 03/01/2022 would be considered as the date of reference where all the unique entries (an entry is denoted by the combination of sno and mmodel) would be considered just delays.

So when I consider 03/02/2022 the common entries between 03/02/2022 and the previous day which is 03/01/2022 would be the delays which are in data (r123,S1234) ,(y123,D2123) and (W321,G345)

The entries existing in 03/01/2022 but not in 03/02/2022 would be considered as off-delays which are in my case (r3421,S1234) ,(g21q,D2123) and (E231,G345).

The entries that are completely new in 03/02/2022 but not in 03/01/2022 would be (D891,S1234) and (K1231,J1231) which are considered as new delays...

So basically

1) Delay Status -The same entry is present in the immediate previous day in comparison to the current day of reference

2) Off delay-The entry from the immediate previous day is not present in the current day of reference

3) New Delay - The entry is present only in the current day of reference and not in the immediate previous day

The Input table is my actual data point. The calculation snapshot that I have shared has the details on how all the current day entries should be compared to the immediate previous day and also all the immediate previous day entries should be compared to the immediate future date (previous date +1; for example current day being march 2nd and the immediate previous day being march 1st (so all entries from march 1st should be included in march 2nd and compared with march 2nd entries (so when arriving at the status for a particular day all the current entries should be compared with the immediate previous day entries and all the previous day entries should be accounted for in the current day...).

Attaching the excel sheet where I have power queries written for the two views -Summary and By model...I want to translate these to Beast modes...Can you please help me here

Delay Status -with Powerquery.xlsx

Sample Input Data - delay.xlsx

calculation explanation.png

MarkSnodgrass

You will have a really hard time wrapping this up in a beast mode since you need to look at multiple rows of data, which requires a feature to be turned on called "window functions in beast modes". Your CSM can enable it for you. Even with it turned on, it's going to be very difficult to put together and maintain.

I would recommend you look at building this logic into a Magic ETL transformation. You can use the rank & window tile and the lag function to determine the different delay status. I recommend reviewing this KB article if you are not familiar with it and try and build just one status at a time.

https://domohelp.domo.com/hc/en-us/articles/360044876094-New-Magic-ETL-Tiles-Aggregate#3.

You may need to use multiple rank & window and formula tiles to get all of the statuses that you need.

Hope this helps.

jaeW_at_Onyx

+1 with @MarkSnodgrass there is no reason to calculate your metrics in beast modes, it would just be a slow card with difficult rules.

You'll want to use a LAG or LEAD function which is explained here in Beast Modes, but the concept is consistent across Magic and SQL: https://www.youtube.com/watch?v=cnc6gMKZ9R8

Given the logic you're trying to create it may actually be easier to implement your solution on smaller datasets using MySQL and variables, but let me know if you can't sort yourself using Magic.

ozarkram

Hi @MarkSnodgrass and @jaeW_at_Onyx : Really appreciate you getting back!..Any guidance on setting up the Magic ETL would be really helpful for me...