Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JASalinas
Creator
Creator

No Accumulation Pivot Table

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!

Labels (1)
  • SaaS

11 Replies
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
Creator
Creator
Author

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:

Captura de Pantalla 2021-10-03 a la(s) 5.23.58 a.m..png

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

Captura de Pantalla 2021-10-03 a la(s) 5.35.22 a.m..png

Thank you very much for you help...

Or
MVP
MVP

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
Creator
Creator
Author

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:

JASalinas_0-1633627257311.png

Without filter all BU_CODE:

JASalinas_1-1633627406737.png

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
Creator
Creator
Author

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
Creator
Creator
Author

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

JASalinas_0-1633634241320.pngJASalinas_1-1633634588573.png

And Next Table With Totals Function:Sum

JASalinas_2-1633634651551.pngJASalinas_3-1633634672007.png

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

Thaks again in advanced