Sum a column - column changes according to number of items on hand

AppSheet AppSheet Q&A
1

Can i add a virtual column to get the total number of items ([category]) in my table?

I have a table - “Utilization Report”

In this table i get data that changes every minute:

[Category] - Type of item i.e. - pump
[Sub category] - Description of that item
[Total inventory] - this changes during the day as customers rent items
[Total out] - changes accordingly
[Total on Hand] - changes accordingly

My question is - how do i get a total of the column - [category]? I want to know how many pumps there are total, out, on hand. I know i can “count” in group aggregate for a table view. But i want a view to look like this:
Total Inventory | Total Out | Total On Hand
Pumps 100 | 30 | 70
4" pump 50 | 40 | 10
6" pump 25 | 20 | 5

Does this make sense?

2

Sorry the table i put doesn’t line up correctly - hopefully you can understand what i am asking…

3

Yes, certainly you can:

SUM(SELECT(Table Name[Total on Hand], [Category] = [_THISROW].[Category]))

Repeat for the other values you want to SUM.

1 Like
4

Thank you

1 Like
5

I only have the one table do i just plug into your expression (utilization report)?

Or do i even put table name - because its my only table for this app?

6

You do need to use the physical table name. You had posted again saying the table doesn’t line up so I was just being general.

Also, you obviously would replace [Total on Hand] with the next value you want to sum up.

1 Like
7

Got it - thank you

8

I added this 3xpression bit it seems to error my app out. I think there are too many rows in my table - over 16,000. Should I just try and do something in the table, instead of this app?

9

Hi Tiger, you could also use SUMIF/SUMIFS expressions in your spreadsheet (assuming that’s what you are using) to calculate the inventory amount and use Appsheet as the data collector and UI for reporting back inventory numbers.

10

What is the error message? If you can, screen shot it and post it here. I don’t think 16,000 rows is an issue.

11

I will try it again and see if i get that error. I think it said “took too long to sync” so i assumed it was the amount of data.

12

“Took too long to sync” is commonly because of virtual columns, not data set size.

13

I used this expression:

SUM(SELECT(Utilization Report[Location_Total_on_Hand], [Category_code] = [_THISROW].[Category_code]))

Utilization Report = Table Name
Location_Total_on_hand = column i want the numbers from
Category_code = string column i need summed

14

That’s the only virtual column in your entire app?

How many rows in the table with this virtual column?

How many rows in the Utilization Report table?

15

that virtual row i think, should go through the entire table (utilization report) which is over 16,000 and sum up the Location_total_on_hand.

So - utilization report - 16,000
this virtual column - 16,000

Right?

16

Oh im sorry- yes its the only virtual column, so far. I will need a few others. Problem i am having here is this. My data table does NOT total anything. So i need totals.

Example:

Generator Totals
Pump totals
etc…

I also need branch (site) totals per item:
Beaumont has 100 pumps
Beaumont has 50 generators
etc…

17

So assuming the Utilization Report table is the table with this virtual column. The table has 16,000 rows. The app formula for the virtual column does a SELECT() against the Utilization Report table itself. That SELECT() examines every one of the 16,000 rows of the table. The app formula is evaluated for every row of the table. This means that for every row of the table, every row of the table is scanned. App formula evaluated for 16,000 rows, each scanning 16,000 rows. 16,000 x 16,000. 256,000,000 rows visited. 256 MILLION row visits for this ONE column.

This is why your sync is taking too long.

Every additional total you calculate this way multiplies the total by another 16,000.

18

Oh no. Ok, then how can i get totals? IS there another way?

19

Ideally, each (branch, item) pair would have its own row in another table and the totals would be computed there. That alone would be a huge improvement. Can you see a path to structuring your app that way?

20

I could create an excel table to get that info - however the original changes data every minute. So i would have to have it also update every minute? Also, can i have one excel spreadsheet send data to another?