cancel
Showing results for 
Search instead for 
Did you mean: 

Adding difference columns to pivot tables

danblack
9 - Travel Pro
9 - Travel Pro

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!

1 ACCEPTED SOLUTION

TriAnthony
Sisense Team Member
Sisense Team Member

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:

TriAnthony_1-1713375417260.png

-Tri

Tri Anthony Situmorang

View solution in original post

13 REPLIES 13

DRay
Community Team Member
Community Team Member

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.

David Raynor (DRay)

danblack
9 - Travel Pro
9 - Travel Pro

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:

danblack_0-1713360335385.png

 

DRay
Community Team Member
Community Team Member

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

David Raynor (DRay)

TriAnthony
Sisense Team Member
Sisense Team Member

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:

TriAnthony_1-1713375417260.png

-Tri

Tri Anthony Situmorang

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
Sisense Team Member
Sisense Team Member

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

Tri Anthony Situmorang

Sure thing @TriAnthony 

danblack_0-1713475012649.png

 

TriAnthony
Sisense Team Member
Sisense Team Member

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

TriAnthony_0-1713476856217.png

 

Tri Anthony Situmorang

@TriAnthony ahhh gotcha, thanks!

It took this time, but unfortunately, got this:

danblack_0-1713478681902.png

 

TriAnthony
Sisense Team Member
Sisense Team Member

@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)?

Tri Anthony Situmorang

danblack
9 - Travel Pro
9 - Travel Pro

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

danblack_0-1714080913507.png

Any idea why that might be?

danblack_1-1714082343595.png

 

 

TriAnthony
Sisense Team Member
Sisense Team Member

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.

Tri Anthony Situmorang

Thanks @TriAnthony !