What is wrong with this expression?

[Tiger1]

SUM(select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [Customer Number] = [_THISROW].[Customer Number]))

Customers_Aging_Exclude_Int = Current Table
Customer Balance = Column in the table
Monthly_Activity_DSO = another table
Final Trx Amount = Column in that table

Here is the error:
Column Name ‘Test DSO by branch’ in Schema ‘Customers_Aging_Exclude_Int_Schema’ of Column Type ‘Decimal’ has an invalid app formula ‘=SUM(select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [Customer Number] = [_THISROW].[Customer Number]))’. Arithmetic expression ‘(CUSTOMERS_AGING_EXCLUDE_INT[Customer Balance]/SUM(MONTHLY_ACTIVITY_DSO[Final Trx Amount]))’ has inputs of an invalid type ‘Unknown’

Column Types:
[Customer Balance] = Decimal
[Test DSO by Branch] = Decimal
[Final Trx Amount] = Price
[Customer Number] = Number

[WillowMobileSys]

Tiger:

select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [Customer Number] = [_THISROW].[Customer Number])

Your inner SELECT() with a division by SUM() doesn’t make sense within a SELECT(). What are you trying to accomplish here?

[Tiger1]

I am trying to find what is called a DSO value:

Total balance / Total Payments * days in that particular month

I have all these values but in two separate tables. I was able to do this using this expression:

SUM(Customers_Aging_Exclude_Int[Customer Balance])/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31

However, I am now trying to break this down PER branch. Branch is a column in my table. I want to be able to select a branch and then see the DSO per that branch.

[WillowMobileSys]

Tiger:

However, I am now trying to break this down PER branch. Branch is a column in my table.

So I’m confused. I don’t see anything in your original expression that is using the Branch column.

What table is Branch in?

What is the relationship between Customer Number and Branch?

[Tiger1]

I am sorry. I was building a Dashboard. I have one table (grouped by Branch) to allow the user to select a branch. Then once they select the branch, i have a detail view from the same table - using that expression i have - to show the DSO per that branch.

Branch is a column in the first table (the table with the expression). Customer Number does not have to do with branch.

Oh wait. that is my problem…

I need to change the expression to:

SUM(select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [branch] = [_THISROW].[branch]))

Correct?

[WillowMobileSys]

You’ll still see the error because of the way you are using SUM within the select.

Tiger:

Total balance / Total Payments * days in that particular month

You provided this formula above. Would it be better defined as:

Total balance by Branch / Total Payments by Branch * days in that particular month?

If so, how do you get?

Total balance by Branch
Total Payments by Branch

[Tiger1]

Oh. You are right. I need to make a VC to get that first… Then i will use the formula you provided. Thanks

[Tiger1]

So i now have this:

[Total Balance by Branch] / Monthly_Activity_DSO[Total Trx Amount by Branch] * 31

[Total Balance by Branch] = VC in Table A
[Total Trx Amount by Branch] = VC in Table B (Monthly_Activity_DSO)

How do i get this to work?

Gives me this:
Arithmetic expression ‘([Total Balance by Branch]/MONTHLY_ACTIVITY_DSO[Total Trx Amount by Branch])’ has inputs of an invalid type ‘Unknown’

[WillowMobileSys]

Tiger:

Monthly_Activity_DSO[Total Trx Amount by Branch]

You would need to perform some SELECT(), LOOKUP(), or have some Ref column to get the value from Table B.

But I wonder, why wouldn’t you just create the VC for [Total Trx Amount by Branch] in Table A where you will be using it?

[Tiger1]

If i do - do i Ref the other table from there?

This is [Total Trx Amount by Branch]:
SUM(SELECT(Monthly_Activity_DSO[Final Trx Amount], [Sales Territory] = [_THISROW].[Sales Territory]))

[WillowMobileSys]

Well, I thought the SUM was by Branch? Now [Sales Territory] is in the mix. If it was just by Branch then you could have just copied the expression over to Table A.

Now I am concerned you are mixing Summation results.

Why are you Summing by [Sales Territory] and not by Branch as was originally discussed above?

[Tiger1]

Sales Territory is branch = i just re name it in the Description.

The Raw data says [Sales Territory] = but my company likes to see “Branch”

[WillowMobileSys]

Ok, what is it called on Table A?

[Tiger1]

Same thing in both Tables. So i change the Display Name in both.

These are the three VC expressions i have so far:

[Total Balance by Branch]
SUM(Customers_Aging_Exclude_Int[Customer Balance])

[Total Trx Amount by Branch From Monthly]
SUM(SELECT(Monthly_Activity_DSO[Final Trx Amount], [Sales Territory] = [_THISROW].[Sales Territory]))

[DSO by Branch]
[Total Balance by Branch] / ([Total Trx Amount By Branch from Monthly] *31)

[WillowMobileSys]

Ok, then it should work. After you created the VC in Table A, did you re-sync the data. The VC updates on a Sync.

[Tiger1]

Yes - i think something is wrong with the [Sales Territory] - referecing the Location Table ( i had to create to change the Sales Territory from a number to a name)

i.e.
Sales Territory 54 = Midland

[WillowMobileSys]

Yes, you need to make sure your data rows are properly updated so the values match between the tables.

[Tiger1]

This is the table for changing Sales Territory into the actual name.

But when i click on the Name -
nothing changes in the detail view below:

Is this correct:
[Total Balance by Branch]
SUM(Customers_Aging_Exclude_Int[Customer Balance])

Or does it need to have [Sales Territory] in it somewhere?

[WillowMobileSys]

It should probably be basically the same format as the expression you copied from Table B…just adjusted for Table A and Customer Balance. So yes, it needs to have Sales Territory, at least based on what I understand of your data.

[Tiger1]

Ok. I made it this now:
SUM(SELECT(Customers_Aging_Exclude_Int[Customer Balance], [Sales Territory] = [_THISROW].[Sales Territory]))

And yet it is still not working…

[WillowMobileSys]

Test the pieces. Show the VC’s in your view and confirm they are getting proper Summed values.

By “not working”, I need to know what you mean

[Tiger1]

I cant show them - because everytime i click on a branch (Sales Territory) there is nothing there. How can i show them?

BTW - this shows up if NOTHING is selected (meaning i do not select a branch).

But… when i select ANY branch it goes back to blank:

[WillowMobileSys]

Ok, how are you building the list you are using for Branch selection?

You need to make sure the value used on the selected Branch row matches the “Sales Territory” values on the rows in Table A and Table B.

[Tiger1]

Yeah. I thought i did. Here is the Table I built to handle the Location (Sales Territory). I built this to be able to put the label on the Name instead of having numbers (key):

Here is a sample of the data i get. If you look at column “Sales Territory” you will see those numbers…

So i am still not sure why it does not work.

Here is the Table ( i built for location) in Appsheets. I put the key as the “Key” and the label is on “Location”. I am not sure what else to do.

[WillowMobileSys]

How is your view “Testing DSO by Branch” constructed? Is it a Dashboard? And if so, what are the views you have included?

[Tiger1]

Yes it is a dashboard:

Select a Branch:

Test DSO by Branch Detail:

[WillowMobileSys]

Ok, you have an Interactive Dashboard. so the Detail view will filter based on the the selection of Branch.

What does your data table for “Customers_Aging_Exclude_Int” look like?

[Tiger1]

Here is the raw data table for - “Customers_Aging_Exclude_Int”:

Here it is in Appsheets:

[WillowMobileSys · Accepted solution]

I don’t see an issue with the way the interactive portion is setup. But remember a Detail view is designed to display only a single row at a time. Your data has multiple rows with the same Sales Territory, so there will be multiple rows in the filter based on the Branch selection - which one is actually shown in the Detail view you can’t really guess at.

Having said that, the VC’s are on each row and should have the same values so it doesn’t really matter which row is shown in the Detail view.

Here’s what I would do to test. Create a Table view for the same data and then add that to the Dashboard as well - maybe replace the Detail view.

Try the Dashboard again and select a Branch to see if you get rows shown in the Table view. This will let you know if the filtering is working correctly or not.

[Tiger1]

Ok. Thanks let me try that and see what happens…

[Tiger1]

Ok. I did that. Do i need to group by location?

So data is coming through - but again when i click on a branch - nothing. But if i do not choose a branch:

I want to add- i do not think the DSO values are correct.

I want to add this as well. I use this formula to calculate the DSO for the entire company (Not based on branch) it works fine.
SUM(Customers_Aging_Exclude_Int[Customer Balance])/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31

Ok - i figured out why the values were wrong. I had the () in the wrong place:

Correct way - [Total Balance by Branch] / ([Total Trx Amount By Branch from Monthly]) *31
Wrong way - [Total Balance by Branch] / ([Total Trx Amount By Branch from Monthly] *31)

However - i still cant figure out why BY BRANCH doesnt work

[Tiger1]

So - i rebuilt the table Location (branch) table. It works now! thanks for all your help.

[Tiger1]

I moved that expression to Table A. But now i am having an issue of selecting by branch (I created a Dashboard with a table of branches and a detail view of this new DSO by branch VC).

It doesnt work. No data shows when i click/choose a branch