Can y’all help me calculate a sum from a lookup column?
I’m trying to calculate the total sales of drinks within a single table. Not sure if this info is helpful, but here’s the background:
A user can create a “recipe” and set the “price”, posts to the “RECIPE” table
The sales tracking page pulls from the “RECIPE” table.
A user can select a “recipe” which posts the recipe name and info to the “SALES” table
I have a couple ITE set up to track rolling sales data based on 1, 7, 31, and 365 days.
I would like to calculate the sales numbers and total sales $ with the same intervals (1, 7, 31, 365). So I created a relation to the table itself to link all the recipes together and then summarize the prices. Now, I don’t know how to actually calculate it.
wrong data structure… you will have so many problems getting your numbers… also 31 days??? months have different day numbers… years too… every 4
how do you collect data for sales? each item separate row? or each receipt? or daily reports for each item?
It’s more of a counter/tally system rather than a true sales system. So when an item is ordered, it’s counted and a new row is added with the date/time. How do you recommend to improve or change the data structure?
Instead of using the logic in the video to create filters, you’d use it to create relations. As @Uzo mentioned, it will require a bit of rework on the data side.
I think you are doing the right thing in the way you collect data (without seeing it), I will assume it goes like that:
row ID
item row ID
timestamp
item name
item QTY
item price
item total
order status
notes/special request
item image
now to sort and filter by time frames (days, weeks, months) you need to add time sort columns:
convert timestamp to YYYYMMDD format and then convert to number, for day filtering
divide timestamp by 7, for weeks filtering. (floor(now-timestamp))/7
extract the month and year from the timestamp in the format YYYYMM for month filtering
extract just a year YYYY for year filtering
Now… I would go with the roll-up sum option… not the relations (faster and easier to deal with numbers)… once you apply the filter, simply do the 4 IF-ELSE columns to find matching filters and bring totals from the items, and use roll-up to sum these totals