What is the best approach? I have a Google ...

[Kelly_VanAusdal]

What is the best approach?

I have a Google Spreadsheet that I have been using for 2 years…in the spreadsheet I calculate the rate of weight gain of my pigs. In my spreadsheet each animal has its own sheet and each row entered is a new date and weight with calculations dependent on the row above. The calculations are: difference in weight since last weigh in, average gain per day, number of days till show and projected weight at that target show.

Here is my spreadsheet (I know its messy): https://docs.google.com/spreadsheets/d/10CZh1t5KKpWHoCx1MfA7s22yJ1465aN146j4BEVDlYs/edit?usp=sharing

I realize that each table in appsheet relates to each sheet in google sheets, so I made a table called Pigs. And a table called weigh in as the table with the formulas.

Problem is when I add a new weigh in, even referencing the pigs, it still calculates that new entry based on the previous entry which could have been a completely different pig.

It does reference the pig, but will have the completely wrong numbers associated with it.

I’ve tried so many different things to get it to filter only pigs with the same name and I can’t get the calculations to work.

Was thinking today, maybe I could use slices to filter new rows only applying to that particular pig? Even then I’m not sure where to add formulas and exactly what those formulas should look like.

Once I figure that part out then I have to move to the next step of projecting weight based on target show dates and how many days till a show.

Thanks for any help!

[Kelly_VanAusdal]

As of my latest attempt. I the following sheets/tables:

Pigs Breeders (pig comes from breeder) Families (pig raised by family) - family is users who will enter weight. Weight Record Weight Calculations Target Show info

My biggest problem is referencing the previous weight when I need to enter a new one to get the difference (gain).

[Vivian_Nowka-Ke]

Hi @Kelly_VanAusdall, One way to go about this would be to use AppSheet formulas instead of the Google Sheet formulas.

For example, if you want to get the weight gain from the last entry for a specific pig, start by creating a virtual column in the Weighs table “last_weigh” with a formula like “MAXROW(Weighs, date_column, [pig_reference_column]=[_THISROW].[pig_reference_column])”

Then, you’ll want to create a virtual column “gain” with a formula like “[weight]-[last_weigh].[weight]”

Hope this helps!

[Lynn]

Hi @Kelly_VanAusdall What do the sheets that you are using in the app look like?

[Vivian_Nowka-Ke]

@Kelly_VanAusdall You already have the column “last weight”, so you can reference it from another column.

So for number of days since last weigh, you could use something like “HOUR(TODAY() - [last_weight].[date])/24”- -because TODAY() - [last_weight].[date] will return a duration like “24:00:00” which is 24 hours, so you can use the HOUR() function to get just the “24”. You want the number of days so you can divide by 24.

[Kelly_VanAusdal]

@Vivian_Nowka-Keane So I tried this several times and I’m missing something in the formula.

It is giving me a large number -737,301.

I made a virtual column for Year w formula Year([Date]) and Month with formula Month([Date]) but that didn’t fix it.

I think I’m just missing something small somewhere…

[Vivian_Nowka-Ke]

@Kelly_VanAusdall Did you try doing what I described in my previous comment with your “Weight Record” table?

[Kelly_VanAusdal]

Sorry Vivian Nowka-Keane, I didn’t initially see your comment.

I will try this. Thanks…will let ya’ll know.

[Kelly_VanAusdal]

Seems every attempt I make to enter a formula fails!

Expression ‘MAXROW(Weight Record, Date, [Pig Name]=[_THISROW].[Pig Name])’ could not be parsed due to exception: #VALUE!.

[Kelly_VanAusdal]

Does the date column require being a timestamp? I would rather it not if possible. In case the user wants to make an entry at a later time that isn’t “Today”.

[Kelly_VanAusdal]

@Vivian_Nowka-Keane I replied below to you.

[Vivian_Nowka-Ke]

@Kelly_VanAusdall The column is not required to be a timestamp, dates work too! Try putting the field Date in double quotes, like “Date” because I believe that’s a Google Sheets keyword.

[Kelly_VanAusdal]

@Vivian_Nowka-Keane Great…Thanks. This has been super helpful.

I had been trying some of these things, but just not having the perfect .",[ etc.

I’m moving on with my formulas…I have “last weight” working and “gain”.

Now working on number of days since last weight.

How would you suggest I set up that formula? I tried:

MAXROW(‘Weight Record’, ‘Date’, [Pig Name]=[_thisrow].[Pig Name]), AND FLOOR(((HOUR([Date][Weight Entry]))(([Year]365.2424.0) + ([Month]30.424.0)))/24.0)

But I think I’m getting confused in what I’m trying to point to.

Once I get that then I will go to Average Daily Gain. Which should be [Gain]/[Number of Days Since Last Weight Entry]

Thoughts?

[Public_Works]

I want to be able to show a today date and a yesterday date. How do i make a formula for this.

I have tried [Date Completed]=TODAY() and can get the today date, but I want Yesterday date too
I have tried [Date Completed]=<TODAY() and can get the yesterday date.
But i’m unable to get Today Date & Yesterday date together. Any suggestions?

[Bellave_Jayaram]

Try OR( [Date Completed]=TODAY(), [Date Completed]=TODAY()-1)