Need help building this expression

Tiger1 · 04-20-2020 07:47 AM

I have three tables in an app. I need information from two table to use in another.

Table A - needed columns:
[Sales]

Table B - Needed Colums:
[DSO]

Table C - i am using these columns.

I need to calculate:
DSO (month in sales) - Payables (same month payables are due)

Table C (So far):

Calculation (so Far - this doesnt work…)

Tried adding [Sales] from Table A - using a key (shows no data…)

I know i am not explaining it well. I just need help on this…

Steve

In plain language, explain what the calculation does, step by step.

Tiger1

I take the (sales) of the month of when my company is (projected) to collect the sales (from today) and minus the payables (by due dates). This will tell the company what their projected income is per month.

Step 1:
DSO - This tells the company how many days from today it NORMALLY collects (for example: 74 days) This means 74 days from today, the company normally collects the sales it did that day.

Step 2:
Payables - these are easy to find. The customers sends the company a bill (with a date).

Step 3:
I need Step 1 - Step 2 = Projected Income for that month.

Steve

How is that month determined?

What does that mean? The sales from today to the projected collection month?

What does that mean? The payables with due dates within the projected collection period?

Tiger1

DSO (Days Sales Outstanding) is what i go by to tell when my company (projects) to collect the Sales they did today( meaning my company rented/sold $100,000 today - but did not collect that money. The DSO (i already have) gives the company a good average of how fast it collects that $100,000) The DSO - is already done. I did that and it works great.

Again - example. DSO = 74 days. This means, if the company takes $100,000 in sales today (credit), in 74 days it should collect that money…

Payables - is done with the invoices the customers give us (customers we buy stuff from - this is NOT same customers that rent equipment from us). On the invoice - we have a date it is due - For example:

Cust A - we owe them $100,000 due in 74 days.

So if i took both examples:
DSO (74 days) and the sales is $100,000
Payables due in 74 days and total is $100,000

Then in 74 days:
$100,000 (SALES based on DSO) - $100,000 (Payables based on customer) = $0

Eventually we want:

(Example) Projected Income:

Jan - $100,000
Feb - $150,000
March - $200,000
etc…

My problem (i think ) mostly lies in doing all of this by month. Because i am getting projected sales (DSO determines the month in the future) and align this month with the payables due month.

Steve

how does Sales Key get its value?

Tiger1

I refer it to Table A “April 2020 Totals”:

This was one way i tried getting [Sales / Invoices] - but didnt seem to work…

Steve

It still needs a value, a key column value in the referenced table. How does it get its value?

Tiger1

I put this column in Table C - to refer to Table A. SO i can then get the value of [Sales / Invoice]. So i am thinking the answer is - no value?

I tried another way of getting the [Sales / Invoices] column but it is a list. I need to be able to mathematically use the values:

I think the expression would be something like this:

IF([Today () + DSO] = [Payable due month], [Sales] - [Payable])

This is the simply version. But i tried this and it doesnt work.

Steve

Yep. You need a value there if you intend to use it.

Which row’s Sales / Invoices column value do you want?

Expression for what?

Tiger1

Rows of [Sales / Invoice] - the total per the month it is projected to come in (DSO determines this)

The expression - is for the calculation i am needing.

Sales (Date determined by DSO) - Payables (date given per customer) = (Projected) Income

Steve

Sales / Invoice is a column. Columns occur in rows. The April 2020 Totals table presumably contains more than one row. Which of those rows do you want the Sales / Invoice column value from?

Tiger1

Sorry- it will be one value (I only want the “Total”) - right now it is $787,337:

The DSO right now = 74

This means the value in [Sales / Invoice] will be collected - July 3 , 2020 (74 days from today).

Right now - this is to ONLY be a monthly projection (not daily - which would be row by row)

Steve

Please engage support@appsheet.com for further help.

Tiger1

ok. Thanks