How to Use Pivot Grid Widget to Split Data Based on Month

Published:

In some scenarios, you may need to split data based on a specific field, such as month, and display the total sum. This can be achieved using the Pivot Grid.

Creating a Data Source

First, you need to create a data source with an API endpoint for Sales by Month. For example,

https://quickbooks.api.intuit.com/v3/company/<realmid>/reports/CustomerSales?summarize_column_by=Month

This data source will contain Name and Month Year columns.

Restriction within the Grid Widget

You can use the Grid widget to view this data. However, you need to bind each Month year columns like Jan 202, Feb 2020 in the value field of the Grid widget separately.

For more details about grid widget configuration, refer to the Grid Widget Documentation.

Using Code View and Pivot Grid Widget

Alternatively, you can use the code view option and Pivot Grid widget to view the same data.

Step 1: Connecting the Data Source

After connecting the Quickbooks data source with the above shared end point, switch to the code view of the data source using the toggle button.

Step 2: Applying the Code

In the code view, apply the following code to transpose Month columns into Rows:

SELECT
"CustomerSales"."8501efdf-e4d9-483f-89d2-9c96d8df0cde" AS "Customer",
UNNEST(ARRAY ['Jan 2020','Feb 2020','Mar 2020','Apr 2020','May 2020','Jun 2020','Jul 2020','Aug 2020','Sep 2020','Oct 2020','Nov 2020','Dec 2020']) AS "Month",
UNNEST(ARRAY [
  '2020-01-01 00:00:00'::timestamp,'2020-02-01 00:00:00'::timestamp
 ,'2020-03-01 00:00:00'::timestamp,'2020-04-01 00:00:00'::timestamp,'2020-05-01 00:00:00'::timestamp,
  '2020-06-01 00:00:00'::timestamp,'2020-07-01 00:00:00'::timestamp,'2020-08-01 00:00:00'::timestamp,'2020-09-01 00:00:00'::timestamp,'2020-10-01 00:00:00'::timestamp,'2020-11-01 00:00:00'::timestamp,
'2020-12-01 00:00:00'::timestamp]) AS "Date",
UNNEST(ARRAY [
"CustomerSales"."d920e079-3604-4689-97b4-422861b72318",
"CustomerSales"."4f21f09d-3960-4a00-a99a-f4ba8868490f",
"CustomerSales"."39d8af71-27b3-45e7-83d6-cb1ea8ad43fe",
"CustomerSales"."f366f4c8-1438-4c6e-bff0-c0d35c2a00b6",
"CustomerSales"."39d39151-6e13-40be-8021-375c0d237b9f",
"CustomerSales"."e467040f-83fc-4210-accf-bc18b2065261",
"CustomerSales"."af8be102-9b4e-40be-91b9-5428cb370f3b",
"CustomerSales"."07445d91-faf9-4b89-8062-20f0f2ad5290",
"CustomerSales"."02ce5d28-f88f-4547-a8d7-50a98f326573",
"CustomerSales"."63c19846-46cb-4aa3-9715-8fafe1812e1a",
"CustomerSales"."63b85b7d-9bae-43eb-8030-e76be780e35e",
"CustomerSales"."dc29b926-a35b-44b0-ac44-5035799cf946"]) AS "Sales",
"CustomerSales"."cf6f222a-da0d-4b7f-88c7-880d630726a0" AS "Total"
FROM "Datasource_a2a4d1bc-5bb7-4515-942c-fdafb2363602"."CustomerSales" AS "CustomerSales"