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 the monthly total columns showing the difference between the column to the left vs. the column to the right.
The only script I found was here (https://community.sisense.com/t5/knowledge/pivot-table-value-difference/ta-p/9197) and it just turns my first column to all zeros.  Has anyone accomplished something like this?

TIA!

  • 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

13 Replies

    • danblack's avatar
      danblack
      Cloud Apps

      Thanks for the reply DRay.

      Unfortunately, the desired behavior is to have average columns alternating in between the month columns.  See the "avg" columns here:

       

      • TriAnthony's avatar
        TriAnthony
        Admin

        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  I got this to work, but for weeks only.  When I try to switch to months, I get that error above.

    I am switching the "Weeks" seen in this screen shot.

    Any idea why that might be?

     

     

    • TriAnthony's avatar
      TriAnthony
      Admin

      Hi danblack,

      I don't see any issue with the formula, if it works with weeks, it should work with months too. Please submit a support ticket so our agent can investigate.