How to sum a table column

[anonymous]

Hi,

I have a table called Equipment List with fields
Item Id - Key
Category,
Description
Current Location
Costs - VC - Related Cost details

I have a table Costs with fields
Item Id - Key
Purchase Date,
Cost
PO No

so the costs show as an inline view in the Equipment View for each Item

What I want to calculate is the total cost of all items.
I want to sum the Cost Column in the Costs table.
In Excel this would be =SUM(Tbl_Costs[Cost])

I have added a VC in the Equipment List Table
Sum([Costs][Cost]).

I want to show a list of
Item Id
Cost
and show the total costs for all these items

It seems very simple, just get the sum of a column but I can’t figure it out.
Any help much appreciated

[JuneCorpuz]

Hello-

Try this adding on your Equipment List table.

SUM(SELECT(Costs[Cost],[Item Id] = [_THISROW].[Item Id])

[anonymous]

Thanks @June_Corpuz, so how do I view this this VC?

I have added a VC to Costs called [Total costs] with expression [Item Id].[Total Costs] where Total Costs is the expression you sent

then added a View showing this Column but it is blank

[anonymous]

This expression sums the related costs but there will always just be one cost per item.
I want to sum all item costs.
Cost could go in the Equipment List table but I want to show it as an inline view so thats why I created a costs table.

[JuneCorpuz]

What you wanted to do is to have an inline view of the Total Cost on the Costs reference table?

[anonymous]

No, I want to sum up the column Costs in the Costs Table and display this, ideally with a list of all the Item Id associated with this total Cost.

Excel

I want to show $894

I have added a table to my Excel spreadsheet [Date] =today() and [Total Cost] : Sum(cost Column in Costs table) and added this table in with a view so I can now see total cost in App but would like to see this on same view with a list of all the item Id’s that make up this total cost.

[hru]

The snapshot is for the detail view of that [Item Id], of which having one Costs Detail. Whiles the excel snapshot is having 3 different Item Id.

Is my understanding is correct?

[anonymous]

I want to show this as a view in the App. It is a simple formula in Excel but cannot figure out how to do this in App. Just this one table with a Sum of the Costs column.

[anonymous]

After much searching, there doesn’t seem to be an easy solution to this. I found a sample App - VirtualCounter from @Aleksi (Thanks @Aleksi ) which I am going to try.

https://www.appsheet.com/samples/Update-virtual-TOTAL-when-records-are-updated-in-the-same-table?appGuidString=4cbaebbb-dbf0-4de7-bc0c-44c8eb07fdf5

[anonymous]

So this is what I have done.

1. Create a new tab in Excel
   Summary Id - Key
   Date
   Total Owned Equipment Cost - has formula =SUM(Tbl_Costs[Cost])

2. Brought this table into App and created a Summary View.

3. Brought Item Description and Category into Costs table using VC

SELECT(Equipment List[Item Description],[Item Id] = [_THISROW].[Item Id])

1. Created a View of Costs table with
   Item Id, Category, Description, Cost

2. Created a Dashboard View showing Summary View and View created in step 4

My next task is to create an action and workflow to PDF and email this view but I’m not sure this is possible. Director would like a monthly report to show this view to compare monthly costs.

[Lynn]

You should be able to do that.

[anonymous]

I have another query on this .

Some Items are classed as High Value in the Equipment List Table. Yes or No.

I have brought this column into the costs table similar to the Description and Category above as a VC.
SELECT(Equipment List[High Value],[Item Id] = [_THISROW].[Item Id])

I want to create a slice of the Costs table for all high Value items but I get the error:

[anonymous]

I have also tried bringing the Item Cost into the Equipment List table as a VC but it is then a list type and not a Price type thinking I could do the slice on the Equipment List instead

Any way to display this as £100.00?

[JuneCorpuz]

Martina:

SELECT(Equipment List[High Value],[Item Id] = [_THISROW].[Item Id])

Go to Slices, add a new slice then add [High Value]=“Yes” on the row filter condition

[JuneCorpuz]

You can use lookup formula change the Type to Price

[anonymous]

Hi @June_Corpuz, that’s what I did and got the error

[anonymous]

Got the Action and workflow to work @Lynn

[Lynn]

@Martina
Great!

[anonymous]

Anyone have any solutions for this please?

[anonymous]

Figured it out

IN(“Yes”, [High Value])