Data Comparison in a DOMO Dataset

yogesh_1
yogesh_1 Member
in Cards, Dashboards, Stories

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

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Answers

  • GrantSmith
    GrantSmith Coach

    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.

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

    @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
    GrantSmith Coach

    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.

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

    @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
    marcel_luthi Coach

    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
    yogesh_1 Member

    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
    GrantSmith Coach

    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.

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

    @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.

    image.png

    Output:

    image.png

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Categories