Best formula to get the number of days since ...

[Kelly_VanAusdal]

Best formula to get the number of days since last entry, filtered by name column?

[Levent_KULACOGL]

@Kelly_VanAusdall Assuming that you have Date column in your table:

TODAY() - ANY(SELECT(TableName[Date],AND([Name]=[_THISROW].[Name],[_RowNumber]=MAX(SELECT(TableName[_RowNumber],[Name]=[_THISROW].[Name])))))

[Kelly_VanAusdal]

@Levent_KULACOGLU

Thanks for your help… It says this is a valid expression…I changed it all to my table and column names.

Some it gives me -17695104:00:00 or …for a 7 day span it gives a result of 696.

For another with a 5 day span it shows 528.

Do I need to have a virtual column for Year and Month? If so, I think I need a formula for that too. Currently I do have virtual columns set up for those as Year([Date]) and Month([Date]) and Day([Date])

[Kelly_VanAusdal]

@Aleksi_Alkio that didn’t work either. Returns 54 for a 7 day span. I even tried /168 the number of hours in a week.

[Aleksi_Alkio]

If that formula doesn’t work, the reason is not the formula itself. The reason is somewhere else.

[Kelly_VanAusdal]

That’s what I assume. Just not sure where to look for a problem

[Kelly_VanAusdal]

@Aleksi_Alkio

[Aleksi_Alkio]

If you send the account ID and app/table/column name to aleksi@appsheet.com, I could check the reason.

[Levent_KULACOGL]

@Kelly_VanAusdall are all the column data is pure date format? MM/dd/yyyy and the type qualifier is set to Date as well?

[Aleksi_Alkio]

One way is the expression MAXROW. Check this article…

help.appsheet.com - List Expressions and Aggregates List Expressions and Aggregates help.appsheet.com

[Kelly_VanAusdal]

@Levent_KULACOGLU @Aleksi_Alkio

I have checked all Date columns to be sure they are in pure date format.

Type qualifiers are set to Date.

I have tried using MAXROW , however I can’t seem to get it to work.

Table name is Weight Record Columns are:

Entry ID

Pig Name

Date

Weight Entry

Last Weight

Gain

Number of Days since last weight entry

Average Daily Gain (wk)

Date of Show Days Until Show

Projected Final

Seems like what I want to do is simple. I know I’m just not entering the perfect formulas.

I need to figure out the average daily gain for each animal. Then from there I will need to project a final weight based on the number of days until Date of Show.

So far I have [Last Weight] app formula =MAXROW(‘Weight Record’, “Date”, [Pig Name]=[_thisrow].[Pig Name])

In Gain column App Formula I have =[weight entry]-[last weight].[weight entry]

Gain from one entry to the next is working using the above.

Now I need the Number of Days since previous “Weight Entry”.

Seems no matter what combination of formulas I use, I just can’t find one that works.

Any help is appreciated.

I had all this working in my google spreadsheet, but one row would depend on the row above and I had all my animals on separate sheets…so I had to change it all up.

[Kelly_VanAusdal]

@Levent_KULACOGLU Do you have any other ideas why I can’t get this to work?

[Aleksi_Alkio]

How about TODAY()-[LAST WEIGHT].[DATE]?

[Kelly_VanAusdal]

@Aleksi_Alkio

Thanks, unfortunately that isn’t working either.

The formula in the Last Weight Column (Ref Type-Ref Weight Record):

=MAXROW(‘Weight Record’, “Date”, [Pig Name]=[_thisrow].[Pig Name])

Here is formula in Number of Days Until Show Column: =Today()- [Last Weight].[Date]

It says the expression is Valid….and is

(TODAY() - The value of ‘Date’ from the row referenced by 'Last Weight’

It returns 1272:00:00 between 7/1/2018 - 7/8/2018 1104:00:00 between 7/8/2018 - 7/10/2018 1056:00:00 between 7/10/2018 - 7/18/2018

I have made sure that the dates are all in pure date format. But is it something with the formatting of the cells?

[Kelly_VanAusdal]

@Aleksi_Alkio

Now I have moved on to working on another table.

Since I wasn’t successful with Date Difference in the app formulas, I decided to just do in the spreadsheet itself.

The spreadsheet is showing the correct number of days difference in google sheets.

Looking at the app, the numbers displayed are completely off.

42 days on the spreadsheet is showing as 1008:00:00 on the app…HELP!

[Aleksi_Alkio]

For exemple 1272:00:00 is a duration and you need to read and convert it to days like HOUR([…] - […])/24