View only showing balances based on date and or/balance

[Tiger1]

I need a view created that will show each transaction(in this case - 3). But i DO NOT need each row to show the (31-60) balance. I ONLY want the row that the transaction occurred in to show the balance.

For example - if a transaction of $1,729.35 occurred in 31-60 days - That is the only row that should show that balance… See image below - this may explain it better.

[Steve]

help.appsheet.com

Slices: The Essentials

Display only certain rows or columns of your table with slices.

[Tiger1]

Ok. I can limit what row is shown, except i need each row here shown. I just do not need the amounts to show up in each column - like they are now. So if i do a slice - wont that limit me to ONLY one row?

[Steve]

How is this topic different from your other post?

Filtering or selecting data Questions

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: [example%20of%20data%20table%20i%20need%20help]

[Tiger1]

Its the same - but i have not found a good solution. Well, i am not understanding it. I cannot get this solved.

[Tiger1]

Sorry. I am struggling to understand how to do this. I still need those rows to show up. So i can’t filter them out. I just need the balance shown (in the appropriate column) each time a row is shown. Right now, that balance keeps showing on each row - whether it occured during that time/date (i.e. 31-60 days) or not

[Steve]

Please describe in words and without using a picture what each column of one row of the table should contain.

[Tiger1]

Each row should show the [Amount] column. Then “$0” where it didn’t happen (did not happen that duration) - i.e. [Current], [0-30 Days], etc…
Then where the [Amount] did happen (the duration) - i need the column to show the [amount].

[Steve]

How is “it didn’t happen” determined, in your own words?

What is the difference between “Current” and “0-30 days”?

[Tiger1]

To me - Didn’t happen means - the [amount] column value was not in that particular duration column.

Current - happened (amount posted) within 15 days (of today’s date)
0-30 - happened (amount posted) between 0-30 days (of today’s date)
31-60 - happened (amount posted) between 31-60 days (of today’s Date)

and so on…

[Tiger1]

[Current] and [0-30 Days] will obviously have some overlap. But this is how my company defines [current] and [0-30]

[Steve]

App formula for Current:

IFS(
  (TODAY() <= ([Date] + 15)),
    [Amount]
)

App formula for 0-30 Days:

IFS(
  (TODAY() <= ([Date] + 30)),
    [Amount]
)

App formula for 31-60 Days:

IFS(
  AND(
    (TODAY() > ([Date] + 30)),
    (TODAY() <= ([Date] + 60))
  ),
    [Amount]
)

App formula for 61-90 Days:

IFS(
  AND(
    (TODAY() > ([Date] + 60)),
    (TODAY() <= ([Date] + 90))
  ),
    [Amount]
)

App formula for 91 and Over:

IFS(
  (TODAY() > ([Date] + 90)),
    [Amount]
)

[Steve]

This is what I suggested. Notice the difference (column name differences notwithstanding).

Steve:

App formula for 91 and Over :

IFS(
  (TODAY() > ([Date] + 90)),
    [Amount]
)

[Tiger1]

Thank you. Let me try these out.

Thanks again, as always!

[Tiger1]

I am still getting same results. Is there another setting or something i may be missing?

[Steve]

Screenshot?

[Steve]

Tiger:

I am still getting same results.

No, you are not getting the same results. In fact, things are improved.

Tiger:

Is there another setting or something i may be missing?

Probably. Please provide a screenshot of the complete App formula expressions for the Current and 91 and over columns.

[Tiger1]

[Tiger1]

[Tiger1]

[Steve]

This is not the expression I suggested.

[Tiger1]

FYI - i labeled [Document Date] - as Display - “Date”

[Current Trx Amount] - as “Amount”

[Tiger1]

OH!!! Am i not suppose to use my dates?

[Tiger1]

Oh. I do see it. I copied pasted wrong expression. Sorry. Let me try this again…

[Tiger1]

Ok. I went back and double checked the expressions. They are what you gave earlier. However it still is not working. I want to point out - i am screen shooting snippets of data. In other words, the other columns are not working either (see image). This dataset is huge so i cannot give all screen shots. The company has thousands of customers and this data set shows all their accounts receivables.

[Steve]

Please provide screenshots of the App formula expressions for whatever columns are incorrect.

[Tiger1]

[Tiger1]

[Tiger1]

[Tiger1]

[Tiger1]

[Steve]

These are all virtual columns? Or are they normal columns?

[Tiger1]

These are normal - straight from the dataset. Should i make Virtual instead?

[Steve]

Ah-ha!

The app formula expressions of normal columns are only reevaluated (the values updated) when the row is modified (e.g., when the row is edited in a form). The app expressions of virtual columns are reevaluated when the app is synced.

Virtual columns would get you quicker updated values, but will extend sync times, especially for a huge dataset. Normal columns wouldn’t have a significant effect on sync times, but require special handling with actions and workflows to keep the values current.

[Tiger1]

Ok. Then i will try Virtual. I am very sorry - i didn’t realize…

[Steve]

Tiger:

I am very sorry - i didn’t realize…

Not a problem. We just had to work through it all.

[Tiger1]

You are always great. I really do appreciate the help…

[Steve]

I went back and looked at your first post on this topic. Am I correct in understanding all of these columns are computed by your database? That you don’t need to compute the values, you just want to hide pre-computed values?

[Tiger1]

Now we are cooking with GREASE!! See image:

[Tiger1]

and - yes you are correct. I needed to just hide based on that [document date] column.

You got me going!!!

Thanks