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.
Here’s what I created, what do y’all think?
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?
By 1,7,31,365, do you mean all sales within the same day, same week, same month, and same year?
yes, exactly same day, week, month, year
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?
Hm. Why you uses Lookup instead Rollup?
It is better i think you have another table with unique Drinks, so there you can summarize by Rollup throw multi relation needed columns
This recent video of mine might help you:
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
- 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.
- 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
Game changer, and as always your tutorials are . I’m updating dates to match this. Thank you!