Summation of values in rows for specific row

[prasad_chougule]

Hi
I am making a food booking app where , different company staff add the item to the cart. Once every staff has added the item to cart , A company admin submit the order.

I am collecting all the orders by different companies in one sheet (mycart) . when company need to pay , i need to show SubTotal amount at that page. But i am unable to do sum for that specific company.

I tried , template, relation, roll up referring to the previous topics here but still could not succeed.
Any help is appreciated.

[Darren_Murphy]

• Create a relation column that matches the Company name with itself
• Do a Rollup->Sum->Price via that relation

[prasad_chougule]

Hi Darren
Thanks. i got hang off it now. However i had one additional question. How can I do sum for different dates and different company .

[Darren_Murphy]

It’s similar, except when you want to match multiple criteria you should use a Query column instead of a relation. Also, when working with dates in this way it’s best to first convert them to integers using a math column. So, to sum by Company and Date:

• Start with a Math column using the following formula:

Year(Date)*10^4
+Month(Date)*10^2
+Day(Date)

• Substitute “Date” in that formula with your Date column. That will give you an integer representation of the date in YYYYMMDD format
• Next create a Query column that targets your table and apply the following filters:
  – Company is This row->Company
  – (math) Date is This row->(math) Date
• Finally, do a rollup->sum via the Query

[prasad_chougule]

Hi Darren Thanks for quick reply.
I implemented as you suggested but it did not sum for same company for same date. See attached picture. For company A it did not work. But for company B it did worked. Am i missing something.

[Jeff_Hager]

You skipped the step to convert your date into a YYYYMMDD integer. You need to consider that your date columns have an underlying time that differs on each row.

[prasad_chougule]

Hi Darren i found error, Its fine now.
I used non math date column.
I have additional question . How can i filter out duplicate values from the rollup column. Same date values for same company.

[Jeff_Hager]

Filter to display DISTINCT (or Unique) rows based on specified columns

I typically use or recommend the following method: Make sure there is a Row ID column. Create a single Relation or Query to link similar rows to themselves based on similar values. Create a Lookup or Single Value column to retrieve the first matching Row ID from the Relation/Query. Finally create an IF column comparing the row’s Row ID to the Lookup/Single Value column and return true if there is a match. Only the first occurrence will be a match.

[prasad_chougule]

Hi Jeff
I tried as per your suggestion above. However it does not single out same rows. It says true to all rows.

[Jeff_Hager]

You already have a query column to handle the grouping. You should be using that and just adding a Single Value column to get the first RowID from each group.

[prasad_chougule]

Thanks Jeff
It worked. I got them sorted. Now i need to figure out how to show them when user login and click them.
It was good learning about relation, query so on.

[prasad_chougule]

Thanks for your support. it’s so fulfilling when some things works and most importantly good quality support.