Creating Quarterly Data Pie Charts Using a Formula Field

2 minutes to read

You can generate pie chart reports for the data you store in Caspio. In this article, we will show you how to create a pie chart that groups records based on a quarter of the year using a Formula field.

By applying the steps below, you can group records by quarter for a given year. We will be using sample sales data as the example for this pie chart.

Before you begin: Ensure that you have a table that stores individual sales with a date in each record.

Sample table design:

Steps:

In Tables, go to Design of your table. Add a new field of Formula data type. In the right panel, edit the formula and paste the following text:
In the pasted formula, replace [@field:Date] with your actual date field.
Name this formula field as Quarter and save.

In your table’s Datasheet tab, verify that the Quarter field automatically generates the corresponding quarter signature based on the date field.
In DataPages, click Create DataPage. Under Charts, select a Pie Chart.
Select your table as the data source.
On the Search Type screen, make sure that the Show Search Form radio button is selected.
On the Select Search Fields screen, move the Date field to the selected fields on the right.
Proceed to the Configure Search Fields section. Select the Date field and change the Precision to Year.
In the Form Element dropdown, make one of the following choices:

- If you want the users to manually type in the year they want to see, select Text Field.
- If you want to predefine the years for users to choose from, select Dropdown and add relevant fields as custom values.

Text field:

Dropdown:

On the Configure Chart Options screen, click Category and select the Quarter formula field as the category field.
Click Value and configure the value to show your data.

Example: Sum of sales per quarter

On the Chart Page Options screen, from the Chart sort order dropdown, select Category(Quarter).
Click Finish.

code1

CASE WHEN Month([@field:Date]) >= '1' AND Month([@field:Date]) <= '3' 
THEN 
'Q1' 
 
 WHEN Month([@field:Date]) >= '4' AND Month([@field:Date]) <= '6' 
THEN 
'Q2' 
 
 WHEN Month([@field:Date]) >= '7' AND Month([@field:Date]) <= '9' 
THEN 
'Q3' 
 
 WHEN Month([@field:Date]) >= '10' AND Month([@field:Date]) <= '12' 
THEN 
'Q4' 
 
 
END