I have a table that updates every minute (Microsoft Onedrive). This table is for invoicing. I have columns that show different customers balances per days:
Current
0-30
31-60
etc…
I need my app to show these however - if the amount doesnt belong in one of the columns i want “0” to show up.
In other words - if a customer got an invoice today and one a month ago. I want the “Current” to only show current see example table i attached:
Those arrows represent the data i don.t want to show up in those columns. They should ONLY show up if they are for that particular amount…
I will assume that the data is raw data pulled from your sheet. Couldn’t you place this expression in each of the “duration” columns - Current, 0-30 Days, etc:
IF([_THIS] = [Amount], [_THIS], 0)
[_THIS] is an AppSheet keyword that refers to the current column.
Can you ever have the same value in more than one “duration” column? If so, how can you know which one shows the value and which one shows zero?
Ok. Thanks i will try this. And yes - you are right, it could equal same value in more than one column. However, with decimals, maybe not often.
Would it be better to check the month (date)?
I used this instead - but it does not work, Any thoughts?
IF([Document Date] = TODAY() - 15, [Current], 0)
[Document Date] = the date i need to check against
[Current] = the balance i want shown if the Document falls within the range…
FYI - Ranges that i need:
Current = today -15 days back
0-30 Days = 0-30 days
31-60 Days = 31- 60 days back
61- 90 Days = 61- 90 days back
90 + Days = everything past 90 days from current date
It’s not exactly clear what data you are loading into AppSheet.
I made the assumption above that you are getting ALL of the duration values shown in your picture from your sheet and that in AppSheet you just want to “clean-up” the view so you only see the Amount column in the appropriate “duration” column by “hiding” the other already present values.
Is this correct? Or are you trying to ASSIGN the Amount column to the appropriate “duration” column based on the Date?
You need this in your Current column:
IF(AND([Document Date] > TODAY() - 15, [Document Date] <= TODAY()), [Amount], 0)
You need this in your 0 - 30 column:
IF(AND([Document Date] > TODAY() - 30, [Document Date] <= TODAY() - 15), [Amount], 0)
and so on.
I tried and it still doesn’t work… See attached.
I used:
IF(AND([Document Date] > TODAY() - 15, [Document Date] <= TODAY()), [Amount], 0)
After making the change, the data IS NOT automatically updated. You would need to access the row in some way for the expression to run and affect the row. Can you bring up the row in Edit mode? You should see the change then.
I guess I’m confused of your question. My data didn’t change and shouldn’t. I don’t need the data to change. I want my table view to ONLY show in the current column (current amount [amount] only), 0-30 days to ONLY show [amount] - that is 0-30 days old, and so on…
Maybe i am confused as to what to do here…
I’m sorry
I am seeing your other post. I’m sorry you are not getting the results you wanted.
I think there are a couple of assumptions you are making that are not accurate and affecting your understanding of how AppSheet operates.
AppSheet does not have different values of data versus display values. If you apply an expression to a column that decides if it uses [Amount] or 0, the UNDERLYING DATA FOR THAT COLUMN IS CHANGED in the display AND in the sheet. If you do not want to change the raw data, then you will need to use Virtual Columns.
When you apply an expression to a column, it does not automatically change the column value unless you perform some kind of edit action on the row so it can re-calculate based on your expression changes.
For example, lets say I have a column Sum calculated with the expression [A] + [B] + [C]. I add a row and Sum column has 83 as the value, it should be 105. I realize I forgot column [D] in the expression. I change the expression to [A] + [B] + [C] + [D].
The column will continue to show 83 until I re-enter that row in Edit mode so that the expression is activated again. If I don’t, the value will never change unless I do so manually in the sheet.
