Data Comparison in a DOMO Dataset

yogesh_1

I have a dataset which stores daily snapshots of data. It is set in append mode, so it captures daily run of that dataset . My requirement is to compare the data between current date and the user input date(which will be older than today's date) and see which are new records / updated etc.

Data is as below:

So latest date is 8th Oct (MM-DD-YY) . If user wants to compare it with 8th oct, he should get that CCCCC order number has been added as new and ship date for XXXXXYY has been changed.

How do i achieve this in DOMO?

SNAPSHOT DATE	ORDER NUMBER	SHIP DATE
10-08-23	XXXXYYY	11-08-23
10-08-23	ZZZYSS	12-08-23
10-08-23	CCCCC	10-08-23
08-08-23	XXXXYYY	12-08-23
08-08-23	ZZZYSS	12-08-23
07-08-23	ABC	01-01-24
07-08-23	DEF	03-04-24

marcel_luthi

First you shouldn't partition on Snapshot Date, you should partition by CSO_Number, as you care about how each Order has changed over time between snapshots, and your Order by should be the Snapshot Date Ascending, so you'll always get the value for that Order on the previous day. Not sure where the SNAPSHOT_DT_FOR_LAG is coming from.

Once you have this, you can compare whether the previous value is the same as the current, if so call it static else if the previous value was null call it new and if it changed then changed that way for each Order you can show the number of times it was changed or created in the period (you filter your table to ignore the static entries). Lag_CSO can still come in handy if for some reason people are able to enter Orders in the system without a Shipping date, or can blank out the date afterwards.

GrantSmith

I'd recommend passing your dataset through a Magic ETL and utilize a Rand & Window tile with the LAG function. You can get the prior record for each order_number ordered by the snapshot date. Then filter your new lag ship date field where it doesn't match the original ship date. This will return records which didn't get updated. Then you're left with a dataset with the snapshot date and all of the orders which have changed or are new.

yogesh_1

@GrantSmith The problem is that user can input any value from the past dates. Its not that he is just comparing previous snapshot with current.

GrantSmith

The lag would show you when a value changed then when they're filtering between the two dates it'll show all the values that changed not just the prior date.

yogesh_1

@GrantSmith

What should be the offset in that case? If I choose 1, wont it give only prior date data? Problem is user can enter any past value and lets say he choose 1st Oct, then it should show records updated /entered new between 1st and Current dat

marcel_luthi

If you're looking at total changes in the given period there are a couple of questions on how you'd address the same record being affected on during the window, examples, assuming Oct 1st as the selected date:

• An order gets created on 10/1/2023 with a ship date of 10/7/2023 and on 10/6/2023 the ship date gets updated to 10/12/2023.
• An order gets updated on 10/4/2023 to a ship date of 10/17/2023 and updated again on 10/8/2023 to a ship date of 10/10/2023.

These are still a single Orders, but they got "affected" multiple times in the window you're looking at. With @GrantSmith approach you could show the number of times a record has the "Change Type" update or New in the period and display those with a pivot table, just by adding a calculated column that tells by comparing the previous and current values if it is new, updated or the same.

yogesh_1

Hello @marcel_luthi . The user will input a date - I have to do a comparison between that date and the current date in the dataset. With @GrantSmith offset=1, how will i achieve it since LAG will show only previous record. Please suggest.

GrantSmith

With the LAG offset = 1 it will return the prior value. You then filter where the lag value and the current value has changed. This will show you when a record has changed.

Once it's filtered you now have a list of all the orders that were either created or updated and the dates associated with those orders. You users can then filter this new dataset based on the date to determine which records are new or updated in between the dates they selected.

yogesh_1

@GrantSmith Below is how I am trying to do it. CSO is Order Number. It is still not giving me expected values. :( . Please see output as well. Now if user selects 2nd Oct , it should show that Ship date has been changed after that. I have no where to show this in resultset.

Output:

marcel_luthi

First you shouldn't partition on Snapshot Date, you should partition by CSO_Number, as you care about how each Order has changed over time between snapshots, and your Order by should be the Snapshot Date Ascending, so you'll always get the value for that Order on the previous day. Not sure where the SNAPSHOT_DT_FOR_LAG is coming from.

Once you have this, you can compare whether the previous value is the same as the current, if so call it static else if the previous value was null call it new and if it changed then changed that way for each Order you can show the number of times it was changed or created in the period (you filter your table to ignore the static entries). Lag_CSO can still come in handy if for some reason people are able to enter Orders in the system without a Shipping date, or can blank out the date afterwards.