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
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.
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.
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
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.
Thank you for your reply and Patience Darren. hahahaha Newbe problems
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
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.
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.
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.
