Consider a formula table based on the 'All Sales' query. In this example, you wish to find the difference in sales between successive fiscal years. Follow the steps below:
1. Create a new report, and add a table with three rows and three columns.
2. Add the following script to the report's onLoad Handler to store the results of the 'All Sales' query: var q = runQuery('All Sales');
3. Select cell[1,0] in the table. Right-click on the cell and select 'Format' from the context menu. This opens the 'Format' panel at the bottom. The second toList argument groups the returned dates by year.
a. Select the Data tab. In the 'Binding' panel, select the 'Formula' option, and enter 'toList(q['Order Date'],'date=year')' as the formula.
b. In the 'Cell' panel of the Data tab, enter 'yr' for the 'Cell Name' of cell[1,0].
c. In the 'Expansion' panel of the Data tab, select 'Expand Cell' to set cell[1,0] to expand 'Vertical'. The Data tab of the 'Format' panel should still be open.
4. Select cell[1,1] in the table.
a. Select the Data tab. In the 'Binding' panel, select the 'Formula' option, and enter the following formula: sum(q['Total@=year(field["Order Date"]):$yr']) In words, this says “for each year in column 'yr', find the 'Order Dates' falling within that year, and sum the 'Totals' for those order dates.” Effectively, this calculates the total revenue generated for a given fiscal year.
b. In the 'Cell' panel of the Data tab, enter 'tot' for the 'Cell Name' of cell[1,1].