I have something like that database structure:
TABLE_PRODUCT
ID | NAME | FK_CATEGORY |
1 | Product 1 | 1 |
2 | Product 2 | 1 |
3 | Product 3 | 2 |
TABLE_CATEGORY
ID | NAME |
1 | Category 1 |
2 | Category 2 |
TABLE_COUNTRY
ID | NAME |
1 | America |
2 | UK |
3 | Portugal |
4 | France |
TABLE_ORDER
ID | DATE | FK_CUSTOMER | FK_COUNTRY |
1 | 10/01/2024 | 1 | 1 |
2 | 10/01/2024 | 2 | 1 |
3 | 10/01/2024 | 3 | 1 |
4 | 15/01/2024 | 4 | 2 |
5 | 20/01/2024 | 5 | 2 |
6 | 10/02/2024 | 6 | 2 |
7 | 12/02/2024 | 7 | 3 |
8 | 05/03/2024 | 8 | 4 |
TABLE_ORDER_ITEM
ID | FK_ORDER | FK_PRODUCT | QUANTITY | TOTAL AMOUNT |
1 | 1 | 1 | 1 | 10 |
2 | 1 | 2 | 2 | 20 |
3 | 1 | 3 | 2 | 20 |
4 | 2 | 1 | 1 | 10 |
5 | 3 | 1 | 4 | 40 |
6 | 3 | 2 | 2 | 20 |
7 | 4 | 1 | 1 | 10 |
8 | 4 | 2 | 3 | 30 |
9 | 4 | 3 | 5 | 50 |
10 | 5 | 1 | 4 | 40 |
11 | 5 | 2 | 3 | 30 |
12 | 5 | 3 | 2 | 20 |
13 | 6 | 1 | 5 | 50 |
14 | 6 | 2 | 4 | 40 |
15 | 6 | 3 | 3 | 30 |
16 | 7 | 1 | 3 | 30 |
17 | 7 | 2 | 2 | 20 |
18 | 7 | 3 | 1 | 10 |
19 | 8 | 1 | 2 | 20 |
20 | 8 | 2 | 2 | 20 |
21 | 8 | 3 | 2 | 20 |
I'd like generate a summary view and I'd like have summary of sales by date, by country, by country and date, by month, by country month with:
Total value sold per category
Total quantity sold per category
Total value sold
Total quantity sold
e.g.
Sumary sales per day
Date | Category | Total quantity sold per category | Total value sold per category |
1/10/2024 | Category 1 | 10 | 100 |
1/10/2024 | Category 2 | 2 | 20 |
15/1/2024 | Category 1 | 4 | 40 |
15/1/2024 | Category 2 | 5 | 50 |
20/01/2024 | Category 1 | 7 | 70 |
20/01/2024 | Category 2 | 2 | 20 |
2/10/2024 | Category 1 | 9 | 90 |
2/10/2024 | Category 2 | 3 | 30 |
2/12/2024 | Category 1 | 5 | 50 |
2/12/2024 | Category 2 | 1 | 10 |
3/5/2024 | Category 1 | 4 | 40 |
3/5/2024 | Category 2 | 2 | 20 |
Total | 54 | 540 |
Sumary sales per Month
Date | Category | Total quantity sold per category | Total value sold per category |
Jan-2024 | Category 1 | 21 | 210 |
Jan-2024 | Category 2 | 9 | 90 |
Total Jan-2024 | 30 | 300 | |
Feb-2024 | Category 1 | 14 | 140 |
Feb-2024 | Category 2 | 4 | 40 |
Total Feb-2024 | 18 | 180 | |
Mar-2024 | Category 1 | 4 | 40 |
Mar-2024 | Category 2 | 2 | 20 |
Total Mar-2024 | 6 | 60 |
Sumary sales per day and Country
Date | Category | Total quantity sold per category | Total value sold per category |
Amรฉdica | 6 | 60 | |
1/10/2024 | Category 1 | 4 | 40 |
1/10/2024 | Category 2 | 2 | 20 |
Portugal | 12 | 120 | |
1/10/2024 | Category 1 | 6 | 60 |
2/12/2024 | Category 1 | 5 | 50 |
2/12/2024 | Category 2 | 1 | 10 |
UK | 30 | 300 | |
15/1/2024 | Category 1 | 4 | 40 |
15/1/2024 | Category 2 | 5 | 50 |
20/01/2024 | Category 1 | 7 | 70 |
20/01/2024 | Category 2 | 2 | 20 |
2/10/2024 | Category 1 | 9 | 90 |
2/10/2024 | Category 2 | 3 | 30 |
France | 6 | 60 | |
3/5/2024 | Category 1 | 4 | 40 |
3/5/2024 | Category 2 | 2 | 20 |
Can anyone help me with this? Thank you.
To start with, you may want to take a look at the sample app Slice based on user input - AppSheet from the help article Get started by using the sample apps - AppSheet Help
The sample app is meant for single user. But you could build one for multiple users by having a Users table.
Hi @Suvrutt_Gurjar,
Thanks for your time, but that doesn't help, because I need to filter information from different tables. I can't do an inner join or use other SQL commands to set up a database view, for example. I would need something a little more complex that could unify summarized data from different tables.
Can you help me about it?
Thank you.
Hi @caburity ,
Frankly, I have not studied your further summary requirements in detail. I will study and revert if I can come up with something useful to suggest within AppSheet. However, if you are doing extensive complex aggregation, you may want to consider utilizing services of some other dedicated service for the purpose such as Google Data Studio/Looker studio where you can link the backend data and build the necessary analysis.
Solved: [Preview] Rich reporting with Data Studio - Google Cloud Community
AppSheet is a mobile app platform with some basic charting engine , fairly advanced report creation automation, bar code , QR code reading capabilities. But still more advanced use cases for anything from reporting , charting to analytics point of view may use external integrations.
AppSheet does provide some basic grouping and aggregation capabilities as well.
Control grouping in a view - AppSheet Help
Thank you @Suvrutt_Gurjar,
I'll have a look in Data Studio to try understand is it should be the solution that I Need. Thank you.
Looker Studio (formerly Data Studio) is your answer.
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |