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!
danblack
Posted 2 years ago·Last reply 2 years ago
13 comments
danblack
OP2 years agoTriAnthony 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?
Tri Anthony
·2 years agoHi 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
OP2 years agoThanks TriAnthony !
DRay
·2 years agoHello 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
OP2 years agoThanks 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
·2 years agoThank you for following up. I'll investigate further and see what I can find.
Tri Anthony
·2 years ago · EditedHi danblack,
You can use the next() function for this. Here's an example formula:
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
OP2 years agoThanks 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
Tri Anthony
·2 years agodanblack, could you send a screenshot of the formula in the formula editor along with the error message?
danblack
OP2 years agoSure thing TriAnthony
Tri Anthony
·2 years ago · Editeddanblack 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.