Why No Totals?

[pkelly]

Hi all

I have the following formula in my Pivot Table to calculate Excess Stock Value.

=IF((sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)) > 0,

(sum({<pro_FamilyCode = {01, 02}> } inm_OnHandValue) / sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty)) *

(sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)),

0)

This is calculating our excess stock value on a line by line basis.

I have click "Show Partial Sums" but none are being displayed.

Any ideas?

Regards

Paul

[alexandros17]

Have you seen on the bottom of the table?

[jonathandienst]

I expect that the conditional is returning false when it is being evaluated at the partial sum level.

You can test this by adding the test expression:

sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)

Does this evaluate to >0? If not, your main expression will return 0.

You could try an expression that use Dimensionality() to make the expression depending on the level at which it is being evaluated.

[sunny_talwar]

You will need to use Sum(Aggr(YourCurrentExpression, YourDimensions))

[pkelly]

Yes, have scrolled down to the bottom and no totals are there.

[pkelly]

I am giving the user the ability to hide / display dimensions - how would this work with that i.e. if I include a dimension in the aggregation but they have chosen not to display it?

[pkelly]

I am getting both positive and negative results - only want to see the positive results.

[jonathandienst]

Paul Kelly wrote:

I am getting both positive and negative results - only want to see the positive results.

In the partial totals as well? Do you get a value when the test expression is positive in the partial total lines?

[pkelly]

Yes I do...

[jonathandienst · Accepted solution]

But then I am not sure if I understand your problem. You say you don't want the values when they (test expression values) are negative and you are getting values when they are positive; so what is missing?

Or do you want the partial total to add the details having a positive test expression value and not the negatives? Then you need

Sum(Aggr(

  If((Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - Sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)) > 0,

  (Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandValue) / Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty)) *

  (Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - Sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)), 0)

  , <dim1>, <dim2>, ....

))

Replace <dim1>, <dim2>, ....with a comma separated list of your table dimensions.