Comparing values based on their update in google sheets

Ashwin_SG

Hello,

I am stuck with a very simple yet hard to design dataflow in domo. So just to elaborate my problem, I have a some google sheets where data gets updated once or twice in a month by our different sales team members. Now they have asked me to build a dashboard where I could show them difference between the values or figures from the latest vs last update in the spreadsheet.

The problem is that there is no date column for reference. I just want to automate the process so that whenever they update the numbers in the google sheet, the dashboard table should update automatically and shows the difference or delta value form the previous update. I do not want to do any manual update in the process. Does anybody has some suggestion for me on how to do this in domo?

I can use google sheets connector to get the latest values in domo. But I have to store the values from last update date as well in some dataset. I am not able to figure out how to do that in a automated way.

Thanks

GrantSmith

Hi @Ashwin_SG

Have you looked into using a recursive dataflow? You can also utilize a date tile or a formula tile to insert the current date and time when the dataflow ran.

Here's a link to creating recursive dataflows using Magic ETL 2.0: https://domohelp.domo.com/hc/en-us/articles/360057087393-Creating-a-Recursive-Snapshot-DataFlow-in-the-New-Magic-ETL

Pros:

• Can filter out duplicate records

Cons:

• The larger the dataset becomes the slower it will run

Another option is to utilize the DataSet Copy connector (https://domohelp.domo.com/hc/en-us/articles/360043436533-DataSet-Copy-DataSet-Connector) with Append update mode to automatically take snapshots of your dataset to then append whenever the copy connector runs. It will automatically add a _BATCH_LAST_RUN_ column containing the timestamp when the copy connector ran.

Pros:

• Fast
• Automatically adds an update timestamp

Cons:

• Possibly have duplicated records if ran multiple times before the underlying dataset is updated.

jaeW_at_Onyx

looooong video about recursive dataflows.

https://www.youtube.com/watch?v=JNQFZCj8JcQ

in any event. you'll want to have a data column. maybe =today() or whatever the equivalent is in your google sheet. then you have something you can run your comparison against when you build your dataflow.

the docs have you do a JOIN. I think I'd recommend doing an APPEND and then use the RANK/Window tile first to deduplicate in case you're running your dataflow twice in the same day. THEN use the RANK / LAG,1 tile to compare current value to previous value.