Total expression in Pivotant Table with aggr

[Anonymous]

Hi there,

We're trying to get total expression results for this formula:

Count(DISTINCT(CUSTOMER)) / CORRECTION_FACTOR

CORRECTION_FACTOR is a number that depends on CENTER and BRAND.

Applying the formula directly, we get these resutls:

It doesn't consolidate for center and brand.

I guess I need to use an aggr function to consolidate, but if I use this formula:

Sum( Aggr( Count (DISTINCT CUSTOMER) / CORRECTION_FACTOR, BRAND, CENTER))

Then the values are not distributed correctly among the AXIS (or any other dimension in the table):

If I add the dimension into the aggr formula, then the values are distributed correctly among the AXIS but the totals are not correct.

Any help or clue on what I'm doing wrong?

Thank you!

[danielrozental]

I think you need to add AXIS to the aggr dimensions

Sum( Aggr( Count (DISTINCT CUSTOMER) / CORRECTION_FACTOR, AXIS, BRAND, CENTER))

[Anonymous]

But if I do that, then it won't do the COUNT DISTINCT when consolidating for BRAND and CENTER, so the results won't be accurate. I mean, if there's the same customer in two AXIS, it will be counted twice when consolidating for CENTER.

[danielrozental]

can you try building a sample app for us to look at?

[Anonymous]

Sure. You may download it from here

As you can see in the app, the column named "Aggr No Axis" have correct totals, but the values are wrongly distributed among the AXIS.

However, when you add the AXIS to the aggr, the totals are wrong, since the expression just adds the values. I guess I need some other function before the aggr, but I fail to know what to use.

By the way, thank you so much for your help.

[#6 · Accepted solution]

IF(dimensionality ( )=1 OR dimensionality ( )=0 ,Sum( Aggr( Count (DISTINCT CUSTOMER)/CORRECTION_FACTOR, BRAND, CENTER)), Count (DISTINCT CUSTOMER)/CORRECTION_FACTOR)

B.R.

Önder

[Anonymous]

YES! THAT'S IT!

Thank you so much!! 😄