topic Re: No Accumulation Pivot Table in App Development

No Accumulation Pivot Table

JASalinas — Fri, 10 Sep 2021 15:21:14 GMT

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!

Re: No Accumulation Pivot Table

rubenmarin — Sat, 11 Sep 2021 08:22:32 GMT

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?

Re: No Accumulation Pivot Table

JASalinas — Sun, 03 Oct 2021 10:38:58 GMT

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

Re: No Accumulation Pivot Table

Or — Sun, 03 Oct 2021 12:27:09 GMT

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?

Re: No Accumulation Pivot Table

rubenmarin — Sun, 03 Oct 2021 15:57:10 GMT

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.

Re: No Accumulation Pivot Table

JASalinas — Thu, 07 Oct 2021 17:24:23 GMT

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

Re: No Accumulation Pivot Table

rubenmarin — Thu, 07 Oct 2021 17:46:50 GMT

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

Re: No Accumulation Pivot Table

JASalinas — Thu, 07 Oct 2021 17:58:39 GMT

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.

Re: No Accumulation Pivot Table

rubenmarin — Thu, 07 Oct 2021 18:35:48 GMT

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.

Re: No Accumulation Pivot Table

JASalinas — Thu, 07 Oct 2021 19:26:07 GMT

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

Re: No Accumulation Pivot Table

rubenmarin — Fri, 08 Oct 2021 06:16:52 GMT

Hi, What I said is to set each sum in a different expression and check wich sum is the one (or ones) that changes it's values when there is only one or more BU_CODE.

First: Identify the issue

2nd: Solve the issue

3rd: Insert again in aggr

Re: No Accumulation Pivot Table

JASalinas — Fri, 08 Oct 2021 14:09:20 GMT

Thanks!...