How do to a combo chart (bars + line)

AppSheet AppSheet Q&A
,
1

Hello,

I have a Col Series chart which has a separation per month. For each month there are three bars displaying different numbers.

What I want is to have, for each month, a line that shows the sum of the three numbers. But I cannot find a way to display the sum as a line. Is it possible to display both bars and lines in the same chart?

As an extra question, how to do a comparison with the previous month? I can create a virtual column to calculate the current month based on the existing data, but I don’t know how to do (currentMonth - pastMonth)

1 Like
2

Hello @Flávio_Geraldes,

A mixed chart, incorporating both bar and line elements, may be developed using quickchart.io No-Code Chart Maker tool. For your reference, a comprehensive list of compatible data integrations is available here: Integrations.

For detailed technical guidance on implementing this tool within your application, please refer to the following documentation: Using QuickChart in AppSheet

Thank you so much! I hope it helps!

3 Likes
3

For this , you may wish to share your table structure of relavant columns.

For example, is there one row in the table per month or multiple rows. And how you are currently adding the per month rows for a total. We presume there will be a date or date time column by default for each row

Using multi row expressions such as a SELECT() or FILTER() in a VC is not a very good idea in Appsheet as it can impact the app’s sync time.

2 Likes
4

The table (in spreadsheet) is simple and straight

Month Value1 Value2 Value3
Jan 10 -35 5
Feb 30 10 -10
Mar 15 2 -5
Apr 22 -13 6
May 45 22 -34
… … … …

In appsheet is easy to create a virtual column that sums the three values (Jan = -20, Feb = 30 , Mar = 12, etc.)

But what I want is to put together with the previous month (Jan = -20, Feb = 10, Mar = 22, etc.)

This I cannot do in appsheet.

Right now I created a column with google spreadsheet who does this calculation. But I would prefer not to have any logic in spreadsheet itself…

5

Thank you.
Is the month column a pure text column manually entered or created from date type column using TEXT() functions.

Also what should be shown in the month of January, because previous months will roll over to the previous year’s December. How the table segregates or identifies between month values of different years?

Based on your response to the above questions, we could opine if and how best an economical expression can be created for your requirement.

2 Likes
6

The month column is a static text.

The month of January just sums the values of January. February sums the values of February plus the total of Jan. etc..

There are no multiple years.

7

Thank you,

The answer below is for your question

Create a column called say [Difference_Previous_Month] of decimal type.

The solution assumes it is an update only table with fixed number of rows , basically one row for each month as you mentioned below

The solution also assumes you have a monthly total column called something like [Monthly_total] of decimal type that adds values Value1, Value2 and Value3

Please create a reference action on the table and make that action as an event save action on form save.

The reference rows expression of the main action will be

SELECT(Table_Name[Key], OR([_ROWNUMBER]=[_THISROW].[_ROWNUMBER], AND([_ROWNUMBER]=[_THISROW].[_ROWNUMBER] +1, [_ROWNUMBER]<14 )))

The condition for this action will be [Month]<>“Jan” . This will ensure the action does not run for the month “Jan” because there is no previous month to compare.

The referenced action that sets the [Difference_Previous_Month] will be of type “Data: Set the values of some columns in this row”. The expression for this action will be something like

[Monthly_total] - ANY(SELECT(Table_Name[Monthly_Total], [_ROWNUMBER]=([_THISROW].[_ROWNUMBER]-1)))

So, whenever a value in any month is changed, the reference action sets the value of the [Difference_Previous_Month] by subtracting current monthly total with the previous one.

2 Likes