Need help with Charts and Graph

Hi Guys,

I am working on charts to display month wise breakdown of my sales. I am using Bar Graph of display it where Y Axis is my invoice value and X Axis is my timeline. I will display it from January to December spanning 12 months.

I need to know wether the approach I am taking is right. Where y Axis I am displaying all invoice values.

X Axis I am displaying money wise like Jan, Feb … March I will be adding multiple components.

I want to know wether this is the right approach?

because In my database I will be creating 12 columns for storing data from Jan to December.

Is this the right approach? is there any better approach?

how will this work if I need to show monthlies earning in a Radial chart? because there I cant seem to find option to ad multiple components

Thank you guys in Advance.

Attaching screenshot for reference


I would flip that 90 degrees.
That is, have a table with 12 rows, one row for each month of the year.
Then a second column with the value for each month.
Use the first column as your X Axis, and the second column as your Y Axis.

My current table has other data as well, Meaning the data for the current UI is coming from table which has 3 rows which has some other data.

how would I add more rows and fetch relevant data from same table?

Please find screenshot of my table

In other words I am using the row ID table to fetch data based on range value.

My dilemma how how do I fetch X axis , (Month value from other table?)

not able to grasp my head around how can I dynamically fetch values for each row differently?

Before we go any further, I have a couple of questions for you:

  • Firstly, that screen shot looks a little suspicious. If I’m reading that correctly, then all you are getting in that row is data for February. If that’s correct, where is the data for the other 11 months coming from?
  • Secondly, how would you expect the chart to behave? Should it:
    – Always show data for the current year, Jan to Dec, or,
    – Show a 12 month rolling window, starting from 12 months ago, or
    – Show a 12 month window, based on a selected starting date, or
    – Something else?

All of the above are possible, but the approach is different in each case. It depends what your end goal is.

Thank you for replying Darren,

There are other columns in the table which give values for other 11 Months. Meaning its a single row which has values for all months.

I couldn’t show it as it couldn’t get covered in the screen capture.

It should show the value for for all months available. Like in my case I have values from Jan 2023 — Feb 2024 , it should show values for all 13 months. I am doing a “Single Value” fetch from multiple columns of different table to get values for each month

Row ID and date shown there as range has no connection to data being supplied to the Graph

Since I only have 1 year data I haven’t added range functionality for it yet. Maybe next step I will consider it


this screenshot should give you a better idea.

Is all the source data in a single table, or somehow spread across multiple tables?

You can simplify this quite a bit, but I’m just trying to get a clear picture in my head before I offer a solution.

If you can show me a screen shot of the source data, that would be helpful.

Source Data is in the single table named Invoice.

What I am doing is storing static value for each months start date and end date

Then I am using query variable to fetch data for that date range.

Below is screenshot attached for same.

Sharing a screenshot for Jan Month 2023. Where I am putting start date as January 1st and End date as January 31st as End date, —> then I write a query to fetch Invoice value within this date range → then do a roll up.

I am doing the same for each month till feb 2024

In next step I am fetching these values into the table I showed previous which had row ID. I am showing each months roll up value there using a Single Value data type.

hehe, okay. You’ve probably got at least 30 columns more than you actually need there :wink:

Give me a little while and I’ll give you a simpler approach.

Thank you for your reply and Patience Darren. hahahaha Newbe problems :joy:

Looking forward to learning from you. Waiting to see the solution.

It important for me to have all this data in table with name X which scrrenshow I shared at the start which has rowID as I fetch lot of data from that table to the UI

Okay, so here is my suggestion:

  • Firstly, in your Invoices table, add a Math column using the following formula:
Year(Date) * 10^2
+ Month(Date)
  • In the above formula, replace Date with your invoice date
  • Now, in the table you want to use as the source of your chart, you need 12 rows and the following columns:
    – A text column, with the month name (eg. Jan, Feb, etc)
    – A number column with an integer that represents the Year & Month in YYYYMM format. eg, for January 2024 it would be 202401. (This can be made dynamic based on the current date, or some other arbitrary date, but we’ll keep it simple for now)
    – A Query column. This should target your Invoices table and apply the following filter:
    — Math (YYYYMM) column equals This row->YYYYMMM column
    – Finally a Rollup column that targets the query column and takes a sum of Invoice amounts

Now should should have the two columns that you need for your chart, as I described in my first response.

If you wanted to extend that for another chart, then it is simply a matter of adding another rollup column. For example, let’s say you wanted a count of invoices by month, then just duplicate the rollup column and change it to do a count instead of a sum.

1 Like

Thank you for the solution Darren. This is how I have structured my table now. One table extra I have put to capture the corresponding month date

Please find the screenshot attached.

I have one question however. how can I make it only have 12 months data?like Since feb has started it should only now capture from Feb 2023 onwards

Sorry, I find that question confusing. Are you saying you want January and February to show no data?

I think we might be back to my earlier question:

Which of the above are you looking for?

– Show a 12 month rolling window, starting from 12 months ago

This one

I selected Limit Number of entries to 12 in the graph options.

hope that is the right way to do it?


To make it fully dynamic is a little more complicated than that.
I’m a little busy right now, I’ll come back to you with a solution later when I have some free time.

Thank you Darren again. Have been learning a lot from you.

Please take your time.

Looking forward to your solution.

If you have a months table already and be able to rollup your numbers for each month, I assume you could sort your chart component backwards and limit the number of items to 12.