Hi All,
After some more help with charts please…
I have a very long table (snippet below) that I want to create various charts from:
| Adviser |
Name |
Customer Type |
Referral Fee |
Job Type |
Total Cost |
Total Profit |
Advice fee |
Find Fee |
Extra Fee |
Referral |
Provider |
Date Submitted |
MM YY |
| Kevin |
Customer 1 |
n/a |
n/a |
Type 1 |
727.38 |
691.011 |
0 |
518.25825 |
0 |
0 |
Prov 1 |
01/11/2022 |
November 22 |
| Jim |
Customer 2 |
n/a |
n/a |
Type 2 |
|
896.6376 |
0 |
672.4782 |
0 |
0 |
Prov 2 |
02/11/2022 |
November 22 |
| Luke |
Customer 3 |
Existing |
n/a |
Type 1 |
66.62 |
63.289 |
0 |
47.46675 |
0 |
0 |
Prov 3 |
03/11/2022 |
November 22 |
| Kevin |
Customer 4 |
Existing |
n/a |
Type 1 |
606.91 |
576.5645 |
0 |
432.423375 |
0 |
100 |
Prov 2 |
04/11/2022 |
November 22 |
| Jim |
Customer 5 |
Existing |
n/a |
Type 1 |
148.9 |
141.455 |
0 |
106.09125 |
0 |
0 |
Prov 2 |
20/10/2022 |
October 22 |
| Luke |
Customer 6 |
n/a |
n/a |
Type 2 |
|
2006.5872 |
0 |
1504.9404 |
0 |
0 |
Prov 3 |
21/10/2022 |
October 22 |
| Jim |
Customer 7 |
Existing |
n/a |
Type 1 |
440 |
418 |
395 |
313.5 |
296.25 |
0 |
Prov 1 |
22/10/2022 |
October 22 |
| Jim |
Customer 8 |
Ex Forces |
n/a |
Type 1 |
412 |
391.4 |
0 |
293.55 |
0 |
0 |
Prov 3 |
23/10/2022 |
October 22 |
| Jim |
Customer 9 |
Existing |
n/a |
Type 1 |
504.09 |
478.8855 |
0 |
359.164125 |
0 |
0 |
Prov 3 |
29/08/2021 |
August 21 |
| Kevin |
Customer 10 |
Forces |
n/a |
Type 1 |
640 |
608 |
0 |
456 |
0 |
200 |
Prov 2 |
30/08/2021 |
August 21 |
| Jim |
Customer 11 |
Forces |
n/a |
Type 1 |
786 |
746.7 |
0 |
560.025 |
0 |
100 |
Prov 2 |
31/08/2021 |
August 21 |
| Luke |
Customer 12 |
n/a |
n/a |
Type 2 |
|
115.14 |
0 |
86.355 |
0 |
0 |
Prov 2 |
01/09/2021 |
September 21 |
| Luke |
Customer 13 |
n/a |
n/a |
Type 3 |
69.98 |
66.481 |
0 |
49.86075 |
0 |
0 |
Prov 2 |
01/01/2020 |
January 20 |
| Luke |
Customer 14 |
n/a |
n/a |
Type 2 |
|
792.4056 |
0 |
594.3042 |
0 |
0 |
Prov 3 |
02/01/2020 |
January 20 |
| Jim |
Customer 15 |
Resub |
n/a |
Type 1 |
160.29 |
152.2755 |
0 |
114.206625 |
0 |
0 |
Prov 2 |
03/05/2020 |
May 20 |
| Kevin |
Customer 16 |
Existing |
n/a |
Type 1 |
944.8 |
897.56 |
0 |
673.17 |
0 |
0 |
Prov 3 |
04/05/2020 |
May 20 |
| Jim |
Customer 17 |
Resub |
n/a |
Type 1 |
150.07 |
142.5665 |
0 |
106.924875 |
0 |
0 |
Prov 1 |
01/05/2019 |
May 19 |
| Kevin |
Customer 18 |
Resub |
n/a |
Type 1 |
77.54 |
73.663 |
0 |
55.24725 |
0 |
0 |
Prov 2 |
02/05/2019 |
May 19 |
| Jim |
Customer 19 |
Existing |
n/a |
Type 1 |
392 |
372.4 |
395 |
279.3 |
0 |
200 |
Prov 3 |
03/05/2019 |
May 19 |
| Luke |
Customer 20 |
n/a |
n/a |
Type 2 |
|
2243.8968 |
0 |
1682.9226 |
0 |
0 |
Prov 3 |
04/05/2019 |
May 19 |
I want to show the cost columns on a chart by month/year… so a table as below for the figures (this is in excel based on [total profit] and shows month/yr) seems to be what is needed - this is in excel with formulas:
|
2019 |
2020 |
2021 |
2022 |
| January |
0 |
858.8866 |
0 |
0 |
| February |
0 |
0 |
0 |
0 |
| March |
0 |
0 |
0 |
0 |
| April |
0 |
0 |
0 |
0 |
| May |
2832.5263 |
1049.8355 |
0 |
0 |
| June |
0 |
0 |
0 |
0 |
| July |
0 |
0 |
0 |
0 |
| August |
0 |
0 |
115.14 |
0 |
| September |
0 |
0 |
1833.5855 |
0 |
| October |
0 |
0 |
0 |
2957.4422 |
| November |
0 |
0 |
0 |
2227.5021 |
| December |
0 |
0 |
0 |
0 |
This lets me create a crass excel chart:
Im really struggling to get this to work in Appsheet. I have the initial table that can be added to etc (the MM YY column auto calculates from the date etc). How do I then convert that data into a chart showing e.g profit by month per year? Ive tried doing formulas in excel then importing that table in but this doesn’t show up in chart view… any advice please?
The actual main data table is thousands of rows deep - so recreating the structure isn’t really something I want to do.
Is there a way of creating a table that sums up the month as the example above that I can then run the charts from??
Please help!
A couple of things.
- However you manipulate data, I do not think AppSheet is capable of giving you the type of chart you have. You need to use an external service like https://quickchart.io/
EDITED: If you search community articles, you can find reporting using HTML. It seems you can do a lot with it but you need to be conversant in CSS and HTML.
- To create a table that stores YEAR-SUMMARY table, that should be fairly straight forward.
You create a table with
ID, YEAR, MONTH, PROFIT.
I suspect you already have this.
It would be kind of complex to fully automate this process. (I suppose it is doable but I would need to give it a lot of thought.)
A simpler way is to populate the profits table with YEAR and MONTH columns manually,
Then you create an action of type - Set the values of some columns of this row and calculate the profit for each row using
SUM(SELECT(raw data table[profit], <<condition YEAR & MONTH>>))
You can run this action either individually or in bulk.
Maybe someone in the community can suggest better ideas..
2 Likes
Thanks for this - this just seems like a massive hole in Appsheets usability.
Most productivity Apps surely need a visual display of figures/trends/costs?
I really need a line chart showing a cost column by month per year for comparison purposes… but not sure this is even possible, such an easy task in excel etc.
I looked at quick chart but I couldn’t figure how to create a live chart from my data. Maybe this has all fried my brain!
Any further help would be greatly appreciated please?
It is possible like the demo I created.
Assuming you have a profit summary table like this…(described in my first reply)
You can build something like this using quickchart.io
This uses the technique described here with a slight modification.
I have shown both the bulk action behavior and a step by step action FYI.
This is the reporting table definition. The chart is shown in a Detail view of this table.
This is where you build the expression to send to quickchart.
Though I built a table for it, Month label can be hard coded because you only have twelve elements.
This is just to extract the unique YEAR values in the profits table
This is the most critical step, Step 2_Build_Year_Data.
As you traverse the YEAR_LIST, you retrieve the year specific data (I did not account for data sparsity here for simplicity) from the profits table and concatenate as you go.
I do not know if you really want to implement this in your AppSheet app or simply export the profit table into Excel and use it to build your graph. If you want to do it inside AppSheet, you need to spend time to learn how AppSheet works so you can maintain it. It is quite “idiosyncratic”. 
2 Likes
It contained more than a few bugs such as that it did not account for the order of data rows entered (ex. JAN, FEB, MAR vs. FEB, MAR, JAN)
It took me a while to fix them. I still have not accounted for sparsity, which could take additional effort to deal with that am not going to do.
Again, as @marsbar mentioned, it is rather a pain in the bxxx to draw charts in AppSheet. But it also greatly simplifies other aspects of app development and one needs to evaluate whether to use this platform or adopt a different solution or combine this with other solutions based on how one feels comfortable with each of the solutions… I will continue to use it for those apps that make sense to build on this platform. Anyway this has been another learning opportunity for me!
1 Like
