Lookup to Text

Ask for Help
1

Hey hey :smiley:

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?

image
image1655×578 170 KB

2

wrong data structure… you will have so many problems getting your numbers… also 31 days??? months have different day numbers… :wink: years too… every 4
how do you collect data for sales? each item separate row? or each receipt? or daily reports for each item?

3

By 1,7,31,365, do you mean all sales within the same day, same week, same month, and same year?

4

yes, exactly same day, week, month, year

5

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?

6

Hm. Why you uses Lookup instead Rollup?

7

It is better i think you have another table with unique Drinks, so there you can summarize by Rollup throw multi relation needed columns

8

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.

9

I think you are doing the right thing in the way you collect data (without seeing it), I will assume it goes like that:

  1. row ID
  2. item row ID
  3. timestamp
  4. item name
  5. item QTY
  6. item price
  7. item total
  8. order status
  9. notes/special request
  10. item image

now to sort and filter by time frames (days, weeks, months) you need to add time sort columns:

  1. convert timestamp to YYYYMMDD format and then convert to number, for day filtering
  2. divide timestamp by 7, for weeks filtering. (floor(now-timestamp))/7
  3. extract the month and year from the timestamp in the format YYYYMM for month filtering
  4. 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

10

Game changer, and as always your tutorials are :100: . I’m updating dates to match this. Thank you!