Calculating the count of entries not present in the future date in comparison to the previous date

ozarkram

Sample Sheet -Off Delay.xlsx

Hi All,

Hope you are doing well!...I am trying to build a magic etl for getting the number of entries (combination of sno and mmodel) that are not present in the current date of reference in comparison to the immediate previous date of reference...This will help understand the count of the number of entries that have gone off the list in the current date of reference in comparison to the previous date of reference... Please find the sample data below:

sno mmodel snapshot_date

r123 S1234 3/1/2022

r3421 S1234 3/1/2022

y123 D2123 3/1/2022

g21q D2123 3/1/2022

W321 G345 3/1/2022

E231 G345 3/1/2022

r123 S1234 3/2/2022

D891 S1234 3/2/2022

y123 D2123 3/2/2022

W321 G345 3/2/2022

K1231 J1231 3/2/2022

r123 S1234 3/3/2022

D891 S1234 3/3/2022

H213 V231 3/3/2022

121k N213 3/3/2022

date off Delay

3/1/2022 0

3/2/2022 3

3/3/2022 3

In the above example for date march 2nd 2022 the number of off delays is 3 because the entries (r3421,S1234) , (g21q,D2123) and (E231,G345) are present on March 1st but are not present in the data on March 2nd

Also the entries (y123,D2123), (W321,G345) and (K1231,J1231) are present on march 2nd but not present in the data on March 3rd so the number off delays for March 3rd is 3...

Can you please help me here...

ozarkram

Hi everyone..Any help on this will be much appreciated!