No Accumulation Pivot Table

[JASalinas]

Hi My Friends...

Iam using a Pivot Table, but I want to avoid accumulations, Can this be done?, How I can do it?

Thanks

Greetings!

[rubenmarin]

Hi, it's not clear for me what you want, Can you post a slice of that table and mark the value you don't want and how you want it to show?

[JASalinas]

Hi @rubenmarin thank for you answer and sorry for answering so far...

I have the next pivot table with filters, if i select the BU_CODE GW1 the value is correct for example:

But if I select two or more BU_CODE then show me a different values in GW1 or WC1 for example:

Thank you very much for you help...

[Or]

Sounds like there might be something wrong with your formula, then. There's no reason for the value to change in this scenario if the formula is something simple e.g. Sum(Field). Could you share the formula(s) in question?

[rubenmarin]

Hi, I agree with @Or, we need to look the expression to know what can change, a TOTAL clause or set analysis or anohter fields used in expression may cause this behaviour.

[JASalinas]

Hi @Or  and @rubenmarin thank you for you answer,  I have analyzed the expression, here i send you the example and the expression that I am using, thank in advanced. With filter BU_CODE GW1:

Without filter all BU_CODE:

And this is the expression:

(Sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {"$(=Year(DatePeriod))"}, [MonthNumPeriod] = {">= {'1'} <=$(=max(MonthNumPeriod))"}, [MonthPeriod]=>
+ <ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {">=1844 <=$(=Year(DatePeriod)-1)"}, [MonthNumPeriod] = {">=1<=12"}, [MonthPeriod]=>}
Aggr(if(sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {"$(=Year(DatePeriod))"}, [MonthNumPeriod] = {">= {'1'} <=$(=max(MonthNumPeriod))"}, [MonthPeriod]=>}[AMOUNT]*-1)
+ sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {">=1844 <=$(=Year(DatePeriod)-1)"}, [MonthNumPeriod] = {">=1<=12"}, [MonthPeriod]=>}[AMOUNT]*-1) > 0,
sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {"$(=Year(DatePeriod))"}, [MonthNumPeriod] = {">= {'1'} <=$(=max(MonthNumPeriod))"}, [MonthPeriod]=>}[AMOUNT]*-1)
+ sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {">=1844 <=$(=Year(DatePeriod)-1)"}, [MonthNumPeriod] = {">=1<=12"}, [MonthPeriod]=>}[AMOUNT]*-1),0), ACCNT_CODE)))

[rubenmarin]

Hi, try to add an expression to check waht value returns $(=max(MonthNumPeriod)) when you select GW1 and if it cahnges when you select two or more BU_CODES or you remove the filter.

Note that $-expressions are expanded before the table, so the value is the same for al dimensions. Set analysis is also calculated for the whole table, not by dimension.

I would also try to split each 'Sum' part of the expression in different expressions to check if there are some that keeps its values and some ohter that changes, so you can focus in the expression that changes it's values

[JASalinas]

Hi @rubenmarin, This is a complete expression, where it does not keep its value is in the Aggr, only when I use the BU_CODE as a filter.

[rubenmarin]

Hi, the aggr has 4 different sums, maybe some changes and some others no. Also you can add these expression in a table with ACCNT_CODE as dimension to simulate what aggr does and look for the changes.

[JASalinas]

Hi, ok, Everything started with the following expression:

(if((sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {"$(=Year(DatePeriod))"}, [MonthNumPeriod] = {">= {'1'} <=$(=max(MonthNumPeriod))"}, [MonthPeriod]=>}[AMOUNT]*-1)
+ sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {">=1844 <=$(=Year(DatePeriod)-1)"}, [MonthNumPeriod] = {">=1<=12"}, [MonthPeriod]=>}[AMOUNT]*-1)) > 0,
(sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {"$(=Year(DatePeriod))"}, [MonthNumPeriod] = {">= {'1'} <=$(=max(MonthNumPeriod))"}, [MonthPeriod]=>}[AMOUNT]*-1)
+ sum({<ACCNT_CODE={"12*", "13*", "14", "32*", "33*", "34", "A*", "E*", "R*"}, [FUND]={"10*"}, [YearPeriod] = {">=1844 <=$(=Year(DatePeriod)-1)"}, [MonthNumPeriod] = {">=1<=12"}, [MonthPeriod]=>}[AMOUNT]*-1)), 0))

In the next Table With Total function : Auto

And Next Table With Totals Function:Sum

This last one is the one i need, I need to do this without Aggr, 

Thaks again in advanced