Simplify an date arithmetic equation?

[Mic_L_Angelo]

Questions folks

I got a formula I’m using to get the number of days between a date in the current row from the date in the preceding row

HOUR(MAX(SELECT(The Feed_Raw[Date], [_THISROW].[_RowNumber] > ([_RowNumber]-1))) - MAX(SELECT(The Feed_Raw[Date], [_THISROW].[_RowNumber] > [_RowNumber]))) / 24

Works fine, but it’s adding 2-3 minutes of sync time (the data set has about 2600 rows). It feels clunky to me

Any ideas to make this equation simpler to cut down sync time? (I need to access all the rows, so I’m not interested in solutions to slice the data sheet)

Appreciate any help

[WillowMobileSys]

[_THISROW] is from which datasource? More importantly, is it from “The Feed_Raw” datasource or some other?

[Mic_L_Angelo]

If you mean what table it’s referring to, it is “The Feed_Raw” @WillowMobileSystems

[WillowMobileSys]

Yes, I as trying to be more general by using the term datasource since we have tables and slices.

I have an idea. I’m trying it out now and will get back in a few.

[WillowMobileSys]

My idea is not panning out. I thought I could force a reference to the previous row by RowNumber without using a select. Either it’s not possible or I’m just not seeing how to do it.

I’m wondering, if the calculation can be performed in a different way?

The dates - are they representing entry dates of the records? If not, what do they represent?

Also, is the result of the calculation stored in an actual column or is it a Virtual Column?

[Mic_L_Angelo]

I hope there’s a simpler answer, this calculation prevents me from designing the app as i want.

Re: dates. The simple answer is yes.
But the actual answer is that we use the app to record when we publish photos. If we add the record to Appsheet when we publish a photo (as we’re supposed to) then this answer suffices. But we may enter the record the day after, so we’ll need to edit the date. So a timestamp wouldn’t work in this case.

The data is stored virtually. I’m trying to get away from extraneous columns in my spreadsheet so the app will run faster. So i would like to avoid empty columns for appsheet to enter data. @WillowMobileSystems

[WillowMobileSys]

Ok, for full understanding then, when you enter the record “later”, which means its not in order, and edit the date, how do you know which other record you want to calculate the number of days from?

I understand what you are saying about extraneous columns. However, Virtual Columns, because of the fact they don’t store results, re-calculate each time a sync is performed. Your expression is performing TWO selects on EACH and EVERY row each time a sync is run. This is slowing the sync tremendously!!

But if you think about it, the Days value only needs to be re-calculated if the Date its based on changes.

If you were to simply make the Days column an actual column and then place your expression in that columns App Formula, you would see a significant improvement, 10-fold+, in the sync time.

I do also want to caution about the usage of RowNumber in your calculations.

It is not a stored value with the row, it is assigned as rows are loaded into AppSheet. I’m not sure how AppSheet loads rows, but typically we should never expect that rows are always loaded in the same order. If that is true, then the RowNumber value may not always be the same for a particular row in the data. In fact, AppSheet’s documentation strongly recommends against using RowNumber and I think it is for this very reason.

This is why I am asking the question at the top of this post. If it can be identified how to perform the calc without depending on row order…and implement the suggested column change…then you’ll be golden!!

[Mic_L_Angelo]

@WillowMobileSystems a bit confused by your question. but to best explain.

We “should” be entering the data in the app in the same order that it is published. The value I wish to get tells me how many days have pass between the last time we posted. If we enter the information chronically as we’re supposed to, then the info I’m looking for will naturally be revealed. If we do not, then we need a simple way to reorganize in our app.

All go my views are organized by the row number. I couldn’t strictly use dates because sometimes we publish twice a day. This is why I relied on the row number. I couldn’t calculate the row number with a sheet formula because if someone entered info in the wrong order, I cant change it in the app.

I’ll figure out a different idea then the row formula. But you reply brings me to a question that is beginning to frustrate me with developing on Appsheet.

As of the moment, my data source contains many columns of metrics that have been imported from another sheet that does their calculations. So my data source is essentially a “flat” file, with no formulas. (I have Delta Sync turned on) It takes about 30 seconds to sync, which is about 15secs longer than I expect

However, I was told by one of the Appsheet mods that my app would perform faster with virtual columns for the formulas, since the app would be syncing less columns.

I my questions is, what is faster?

Synching flat-files with many columns?
Or using virtual columns with app formulas?
Or using actual columns with app formulas?

That answer will help determine what I should structure my data source

[Mic_L_Angelo]

as an addendum,

the reason why I suspicious of adding more columns with values is because of the 30-second sync, even if I’m only adding a new row.

I don’t know if Appsheet is counting the imported values on the backend as a “change” and just re-synching all the rows anyway.

[WillowMobileSys]

Right. While I am answering your questions above, I encourage you to use the Sync Analyzer in AppSheet. You’ll find it in the bottom right-hand corner of the emulator. It will tell you where the majority of your sync time is focused.

[WillowMobileSys]

First, I understand your frustration.

I do want to point out that the decision to calculate or store the values is NOT isolated to AppSheet. I have been a professional software developer for over 25+ years and every development platform has this problem.

Unfortunately your questions cannot be answered in a general sense because it depends on what kind of calculations that are being performed, how the results need to be used and your primary goals in the app - fast response, flexibility, small app size, etc

Depending on what your implementing, you may need to make trade-offs to achieve the goal. Trade-offs could be achieved in many ways.

For example, if it was a set of a dozen calculations based on other values in the SAME row. Absolutely, I would use Virtual Columns and not store them in the sheet.

However, if I needed those same 12 calculations to show in a report, then I would need to store them to be used later.

In your case, the Virtual Column needs to search the entire table TWICE to find the two rows it needs to perform the calculation. As the table grows, so does the time it takes to perform those two searches.

So, to compromise and improve user experience, you would want to store this particular value.

[Steve]

If you don’t need the value to dynamically recompute and are satisfied with it updating only when the row is updated, create a normal (not virtual) column to hold the computed value. That computed value will then have no significant effect on sync time.

[Mic_L_Angelo]

ok, noted. thanks guys @Steve @WillowMobileSystems. I guess I should avoid virtual columns

So here’s my last 2 question about this.

1. Does Appsheet view “change” as a change in value or if the value is simply re-added?

As I mentioned before, I have calculations in my sheet imported hourly from another spreadsheet (I’m using Google Appscript).

Do either of you know if Appsheet determines the imported values are a “change” even if the value is the same?.

2. If the answer is “no”, is there any benefit to formatting physical cells with app formulas when I already have my desired calculations from Google Appscript?

I’m still perplexed why the sync takes as long as it does, even though only one row is changing…

[Steve]

Mic_L_Angelo:

1. Does Appsheet view “change” as a change in value or if the value is simply re-added?

As I mentioned before, I have calculations in my sheet imported hourly from another spreadsheet (I’m using Google Appscript).

I believe a change is a different value sent from an app to the AppSheet server. I don’t believe workflows (for instance) will notice a data source change that doesn’t originate within the AppSheet framework. Specifically, I don’t think AppSheet would see the changes resulting from your hourly import as “changes”.

Mic_L_Angelo:

Do either of you know if Appsheet determines the imported values are a “change” even if the value is the same?.

I don’t know. Should be easy to test.

Mic_L_Angelo:

2. If the answer is “no”, is there any benefit to formatting physical cells with app formulas when I already have my desired calculations from Google Appscript?

If the data is computed elsewhere and is static within the AppSheet scope, I see no reason for AppSheet to compute it.

[WillowMobileSys]

@Mic_L_Angelo First, I wouldn’t say you need to avoid Virtual Columns. They have tremendous value and when to use them or a “normal” column is not straight forward…it will depend on your needs. Experience will be your best teacher but if you are ever uncertain just ask here in the Community.

To strengthen what @Steve has said.

AppSheet does NOT react to changes made to the sheet from other input sources. What AppSheet does do is track changes made within the app. Those changes will be posted to the sheet, AppSheet will wait for any formulas to complete and then will re-sync the data between the sheet and the app. ANY changes by AppSheet or otherwise will be pulled into the app by the Sync.