How to Calculate Percentage Change From Last Entry to Most Recent Entry?

Tony_Insua
Participant V

Hi Gurus,
Surprising I have not been able to find this on the help docs or community, but what is the expression used to calculate the percentage change from the Last Entry to Most Recent Entry in a Column ?

I know the formulation in google sheets is simple ([Current Entry Num]) - [Last Entry Num] / [Last Entry Num] with a percent field.

There is always a problem with the first entry ever because there is no previous value to refer to. In google sheets I wrap it with an IFERROR(formula, 0 which then leaves a 0% value for the first entry. Is there an equivelant expression in AppSheet?

Thanks!

Not sure how to reference these values.

Thanks in advance!

Solved Solved
1 18 854
1 ACCEPTED SOLUTION

@Tony_Insua
Geez, I now understand your issue. Create a Virtual Column with MAXROW() expression first:

MAXROW("Tablename", "DATE ENTERED")

This Virtual column will return you the reference of the row with the latest entered date in the table where you can use it to dereference the value of the FTP column in the table. Now you can apply below expression for your [FTP % CHANGE] column:

IFS(
    ISNOTBLANK([VirtualColumnName]),
    ([FTP] - [VirtualColumnName].[FTP]) / [VirtualColumnName].[FTP]
)

This will give you the % change of the FTP value as per the lates FTP value in your table. I have also updated my sample app as such, so that you can see it running.


APPSHEET DOCUMENTARY



View solution in original post

18 REPLIES 18

Please take a look at a post with similar requirement below

LeventK
Participant V

@Tony_Insua
You can try with this:

([_THISROW_AFTER].[ColumnA] - [_THISROW_BEFORE].[ColumnA]) / [_THISROW_BEFORE].[ColumnA]

Nice one @LeventK

With the recent discussions on [_THISROW_AFTER] and [_THISROW_BEFORE] being available in normal app expressions also, yours will become much more complex solution.

Thanks @Suvrutt_Gurjar,
Your contributions to the community are outstanding as well my friend. As @Steve also expressed in one of the posts, we are a wonderful team here. Godspeed!

Hi @LeventK,
Thanks for your suggestion! Looks like an elegant expression though I tried it and it did not result in the the expected solution with the result is zero. I have a screen shot of the test to confirm the formulation above and the result for FTP % CHANGE column. Any thoughts on thus?

@Tony_Insua
The expression will work when you edit the row and change the value of [FTP] column. For an existing record, you cannot calculate a value because the previous value is not here neither in the cache. So unless you donโ€™t edit a specific row and change the value of [FTP], the calculation result will always be 0. To test it; just edit the record in 2nd row and change the value of [FTP] to 300. You should now see the calculation as 20%.

Okay, I had thought the cache was the problem so I deleted all of the previous FTP data and created new FTP data and my result is still zero percent. Itโ€™s showing a percentage but 0.0% is not the correct one. I donโ€™t doubt the expression. Iโ€™ll continue to investigate.

Thanks

Hi,
Could there be something wrong with the set up of my column? Any sample apps you can suggest that has this particular formulation?
Cheers

3X_b_9_b9df70f28c7c81aca6d0bb03da77efbc1ba2dd28.png

3X_b_8_b8e322fcab0defdc1b2b337d11240ead89f27b03.png

@Tony_Insua
Your set-up is correct, no doubt with that. If you want you can try with this expression as a workaround, but I repeat, it will only work when you EDIT the record.

([FTP] - LOOKUP([_THISROW].[FTPID],"TableName","FTPID","FTP")) / LOOKUP([_THISROW].[FTPID],"TableName","FTPID","FTP")

This didnโ€™t work for me for whatever reason. Iโ€™m still getting the 0.0% value. Iโ€™m going to create a test application outside of this and create a simplified version. Iโ€™ll create a DATE, REVENUE, and Revenue % Change and see if these work. Iโ€™ll let you know how it goes!
Cheers

Hi @LeventK,
Iโ€™ve created a simple TEST APP and the formulation was not working on the test app. Hereโ€™s a link to the app. You may be better in helping to see what the issue is with the original formulation. Try creating a new record too.
Thanks

([_THISROW_AFTER].[ColumnA] - [_THISROW_BEFORE].[ColumnA]) / [_THISROW_BEFORE].[ColumnA]

[/quote]

@Tony_Insua
I cannot see that as it requires user login and Iโ€™m not a user of the app. Please add my email (levent@able3ventures.com) as a co-author with edit access to this test app and I will take a look.

@Tony_Insua
Here is the proof of concept and you can try it from my sample app below with clicking your name on the main menu


APPFORMULA EXPRESSION


IF(
	IN([ID],PercentChange[ID]),
	([Number1] - LOOKUP([_THISROW].[ID],"PercentChange","ID","Number1")) / LOOKUP([_THISROW].[ID],"PercentChange","ID","Number1"),
	NUMBER("")
)

SAMPLE APP


@Tony_Insua
I have also fixed your TEST APP that you have shared. I have edited the first record where the revenue was $2,200, changed the revenue to $2,500 and [% Change] correctly calculated as 14%. You can see it in you back-end as well.

Thanks LeventK for really clarifying this expression. This is such a supportive community. I wanted to see the inner workings of the formulation by adding new data. I didnโ€™t touch the expression but I can no longer see the % change anymore. I should clarify that Iโ€™m looking for the % change from the previous entry to the most recent entry. My apologies for the confusion. The word โ€œLast Entryโ€ was incorrect in that it should be โ€œPreviousโ€.

@Tony_Insua
Geez, I now understand your issue. Create a Virtual Column with MAXROW() expression first:

MAXROW("Tablename", "DATE ENTERED")

This Virtual column will return you the reference of the row with the latest entered date in the table where you can use it to dereference the value of the FTP column in the table. Now you can apply below expression for your [FTP % CHANGE] column:

IFS(
    ISNOTBLANK([VirtualColumnName]),
    ([FTP] - [VirtualColumnName].[FTP]) / [VirtualColumnName].[FTP]
)

This will give you the % change of the FTP value as per the lates FTP value in your table. I have also updated my sample app as such, so that you can see it running.


APPSHEET DOCUMENTARY



This worked beautifully. Thanks for taking the time!
Cheers

Youโ€™re welcome, my pleasure to be helped of.

Top Labels in this Space