Adding difference columns to pivot tables

[danblack]

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!

[DRay]

Hello @danblack,

Thank you for your question.

You may be able to use Formulas with Functions. Can you look that at this documentation and let us know if it works for you? https://docs.sisense.com/main/SisenseLinux/build-formulas.htm

Thank you.

[danblack]

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:

[DRay]

Thank you for following up. I'll investigate further and see what I can find.

[TriAnthony · Accepted solution]

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]

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]

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

[danblack]

Sure thing @TriAnthony 

[TriAnthony]

@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.

[danblack]

@TriAnthony ahhh gotcha, thanks!

It took this time, but unfortunately, got this:

[TriAnthony]

@danblack looks like you might have a modeling/design issue. Is the column visit_date in the same table as Billable Hours? Is it the same date field used in the Columns panel of the pivot table and in the dashboard date filter? Do you have other formula in the widget that includes column(s) from other table(s)?

[danblack]

@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]

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.

[danblack]

Thanks @TriAnthony !