Forum Discussion

danblack's avatar
danblack
Cloud Apps
04-15-2024
Solved

Adding difference columns to pivot tables

I simply have a pivot table where the first column contains a varchar and the last column contains grand totals.  All columns in between are monthly totals.  I would like to add columns in between th...
  • TriAnthony's avatar
    TriAnthony
    04-17-2024

    Hi danblack,

    You can use the next() function for this. Here's an example formula:

     

    (
  <your aggregation formula> + 
  CASE WHEN ISNULL((<your aggregation formula>, NEXT([Months in <your date field>], 1))) THEN <your aggregation formula>
       ELSE (<your aggregation formula>, NEXT([Months in <your date field>], 1))
  END
)
/ 2

     

    This formula assumes for the last month, since there is no more column to the right, the averages would be the same values as that month's column.

    Here's a how the results look like with Sample ECommerce data:

    -Tri

TriAnthony's avatar
TriAnthony
Admin
04-17-2024

Hi danblack,

You can use the next() function for this. Here's an example formula:

 

(
  <your aggregation formula> + 
  CASE WHEN ISNULL((<your aggregation formula>, NEXT([Months in <your date field>], 1))) THEN <your aggregation formula>
       ELSE (<your aggregation formula>, NEXT([Months in <your date field>], 1))
  END
)
/ 2

 

This formula assumes for the last month, since there is no more column to the right, the averages would be the same values as that month's column.

Here's a how the results look like with Sample ECommerce data:

-Tri

danblack's avatar
danblack
Cloud Apps
04-18-2024

Thanks for the reply, TriAnthony.

However, I'm unclear as to where that code goes.  It doesn't work in the widget value formula.
This produces "Function Syntax Error: Unexpected token":

(
sum([Total Billable Hours]) +
CASE WHEN ISNULL((sum([Total Billable Hours]), NEXT([Months in visit_date], 1))) THEN sum([Total Billable Hours])
ELSE (sum([Total Billable Hours]), NEXT([Months in visit_date], 1))
END
)
/ 2

  • TriAnthony's avatar
    TriAnthony
    Admin
    04-18-2024

    danblack, could you send a screenshot of the formula in the formula editor along with the error message?

      • TriAnthony's avatar
        TriAnthony
        Admin
        04-18-2024

        danblack Thanks for sending the screenshot. The formula is invalid because the Month field (Months in visit_date) is in plain, unlinked text. To add a column to a formula, you have to select it from the list in the Data Browser tab, instead of manually typing it. See screenshot below for an example.

        Please replace the two instances of "Months in visit_date" in your formula by the actual column, selected from the list. Note that a valid column selection is shown in blue, whereas typed-in column name is invalid and shown in dark grey.