SUM values of identical items in a list of products

[Guy_dataclayfor]

Hello and sorry for my poor English language :)I

I spent a long time to try alone with no success. Any help will be very appreciated!
I want to summarize shift daily and annual work making pallets of products.

I created  tables for "shifts", "production", "products" and "summary" (or report)
In "summary" I want to display a list of product with the quantity of pallets made.
I tried to search and select in a list from  "production", the different products with the corresponding amount of pallets to make calculation on them.
However, I have now 50 Products which could increase later. How can i solve my problem? SUM(SELECT(production[Total Pallets],[Product]=??? ))
Have I to make a Virtual Column for each product which is a lot or is there another option ? I hope the attached can help to understand my need.

SUM of different product pallets per day
SUM total

Thanks a lot for any help.
Kind regards
Guy

[TeeSee1]

I tried two different approaches.

1) An "ugly" way to do it within AppSheet, The trick is to make a combined key in the Report table so when rows with duplicate keys are added, the system automatically deletes unnecessary rows but as you can see in the video, it takes sometime to update the table.

2) My recommendation - Use Lookerstudio. Straight forward. No coding whatsoever..

Let's see if others have more ideas..

[Guy_dataclayfor]

Hi TeeSee1
Thank you for your kind reply.
I'm not an expert even if I already made several apps using Appsheet which I'm a bit familiar with now 🙂
I don't want to use another tool because I don't have time enough to learn something new now. Anyway, could you please share the formula you used to make it work the "ugly way" please? Thanks again for your time 👍
Guy

[TeeSee1]

1. Create a table with USEREMAIL as its key and a date column (Control in my example)
2. In your report table, create a VC with CONCATENATE([date], [product]) expression and designate this as the key in addition to what you want to see.
3. Create an action on Production of type Data: add a new row to another table by using values from this row with the following expression in the SUM column. Date and Product expressions are simple copies.

   SUM(
    SELECT(
     Production[Total Pallets],
     AND(
      [Date] = [_THISROW].[Date],
      [Product] = [_THISROW].[Product]
     )
    )
   )

4. Create an action on the table created in step 1 of type Data: execute an action on a set of rows and run the action in step 3. Referenced rows should be

   FILTER(
    "Production"
    [Date] = [_THISROW].[Date
   )​

    

Now I have not implemented the next step in the previous demo, which is to recalculate summary data. You need to delete the relevant rows first (filter with date) and run the creation step as above. You can create an action to run them both sequentially. (delete and create).

When I tested this in the new desktop mode (still in beta) , the second step, data creation, fails due to a duplicate key error. This happens even when I manually execute the two steps separately. It works fine in the current mode and this is something you want to be aware of.

[Guy_dataclayfor]

Again, thank you very much for taking time to reply. I will try and send you feedback. Enjoy your day.
Kind regards

Guy

[AleksiAlkio]

Just another idea.. would it be enough to create two table views and group them with a proper column. If you for example group the view with the date and then with product, the aggregate with the Pallets would then show them properly.

The annual summary could be done when grouping Year and Product.

[Guy_dataclayfor]

Hi AleksiAlkio,
I just posted a few more info. In the same time than you😀.
Thanks also for your kind help.

[AleksiAlkio]

One workaround.. https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Generate-an-Attendance-report-in-multiple-rows-...

[Guy_dataclayfor]

Dear AleksiAlkio

Thank you for your link to your template. When I found it, I thought I could have used it but I met some issues.
I'd like to show you my way and issues... I f you can find any solution I will be grateful 🙂 Sorry for such a long post but I can't see how to describe my issue in another way 🙂

Attached is a view of my Table “ProdLine”. Each time a shift is completing a pallet, a row is added. Same product can be added by different shift the same day. I need the sum of products (Could be 100 different prod).

I also attached A view resulting from a simple List [Related ProdLines] in my "SUMMARY" table from [Summary-Ref] in "ProdLine"

Below are the formulas I used, based on your template:
You will notice in red in the table result my issues.
-The same product is displayed more than once
-The Column [Total Pallets] does not dispay the total of the occurences of the same product.
-Same for the Total Unit

Product:
<<Start: ORDERBY(
SELECT(ProdLine[ID],AND([ID]=MAXROW(“ProdLine”,”Date”,[Product]=[_THISROW-1].[Product]),[Date]=[_THISROW].[Date])),
[Date],TRUE,[Product])>>
<<Product>>

Date:
<<IF(
ISNOTBLANK(ANY(SELECT(ProdLine[Date],AND([Product]=[_THISROW-1].[Product],[Date]=[_THISROW].[Date])))),
ANY(SELECT(ProdLine[Date],AND([Product]=[_THISROW-1].[Product],[Date]=[_THISROW].[Date]))),
“N/A”)>>

Pallets:
<<ANY(SELECT(ProdLine[Total Pallets],AND([Product]=[_THISROW-1].[Product],[Date]=[_THISROW].[Date])))>>

Units:
<<ANY(SELECT(ProdLine[Total units],AND([Product]=[_THISROW-1].[Product],[Date]=[_THISROW].[Date])))>>
<<End>>

I would like to display similar items (product) in a list only once and display the SUM (of Pallets and units) for each items.

Thanks again for your kind help.
Guy

[AleksiAlkio · Accepted solution]

It needs something like this..

<<Start: ORDERBY(SELECT([Related Prodlines][ID],[ID]=MAXROW(“ProdLine”,”DateTime”,AND([SummaryID]=[_THISROW].[ID],[Product]=[_THISROW-1].[Product]))),[Product],FALSE)>><<[Product]>>

[Guy_dataclayfor]

Dear AleksiAlkio

Thousand thanks ! IT WORKS fine!
when I think of the time I've spent trying everything to no avail....
Dot between field or no dot.. that was the question 😁 (one of them)
There's no doubt about it, knowledge is power!

Thanks again to you AleksiAlkio and to TeeSee1 for your kind help
Best regards
Guy

[Guy_dataclayfor]

Dear TeeSee1,

I spent some time again trying to find a simple solution. I understand your proposition and thank you for it. However, I think that my question was not clearly explained. I have to admit that it sometimes  takes longer to describe a problem than to find a solution.
I hope that the image below will clarify my main needs.

My final goal is to display the content of a list of data , from "Production" table using an email report from "Summary" Table

I would like the list of data to be displayed in such a way that
- when the same product occurs more than once, it should only be displayed once, with the sum of iterations for these same product.

I've tried a lot of formulas with no good results.... I'm desperate! Especially if there is a simple solution wich I miss!!! Please notice that I don't need any action trigger. I create rows in my "shift" table which already creates as many rows as necessary in "Production" table. And because there are several Shifts in a day, the production table may contain several times the same product... 🙂

In summary "How to display similar items (products) in a list only once and display the SUM (of Pallets) for each items"

Of course any formula is welcome including within the report itself as shown below.

Thanks a lot for helping me 😀
Guy

[TeeSee1]

This is exactly what I showed in my previous post, MGRep being your Summary equivalent, except that I do not implement this in a bot. You can simply get required data from that table and send it in email.

I do not think you can create a single expression to do this, unfortunately.

I still think Looker Studio which is readily available and easy to configure (at least in this case) is the best solution. The only thing you need to do is to create a report once and share the link. If you have a web site that can run an iframe, you can publish the up to date info just as easily.

[Guy_dataclayfor]

Thank you TeeSee1.
Unfortunately I miss time... I will try again this week. Meanwhile I also checked AleksiAlkio suggestion. I tried it also because it looked more familar to me but I faced some issues. Check out my reply to him. I'll post my feedback and result (I hope) Kind regards. Guy

[TeeSee1]

I believe @AleksiAlkio 's solution is excellent!

[Guy_dataclayfor]

Thanks a lot for your kind help TeeSee1. Yes it works fine!
Have a great day
Guy

[TeeSee1]

Just FYI.

@AleksiAlkio 's solution together with your schema inspired me with a new solution which is much cleaner. 

I have added a child table to Summary and capture consolidated data there.

You can also run a bot off of Summary for off line reporting easily like

Cheers!

[AleksiAlkio]

As we know, there are always more than just one solution 😀