Disparity in totals when dimensions are selected in list boxes

[BrunPierre]

Hi Guys,

So I have an AGGR function in a table to count the number of Customer IDs distinctly. Also, as a dimension is Customer Type, but the individual selection of the Customer Type does not tally with the total in the table.

I am still trying to figure out why that is so and could use some help.

Thanks.

[benvatvandata]

Could you share your exact expression? Also any screenshots of the issue would be helpful too.

[BrunPierre]

The expression is as follows;

SUM(AGGR(COUNT({$<Year={'$(vMaxYear)'}>}DISTINCT CustomerIDToCount),CustomerIDToCount))

The above is the total in a table.

 However, selecting from the customer type list box one after the other results in differences in the totals.

Hope this provides more clarity on my issues.

Thanks

[Kushal_Chawda]

I am not sure why you need aggr here. Can you not simple try without aggr? Just a inner count expression

[BrunPierre]

The table below shows the difference using AGGR and simple count distinct;

[Vegar]

It looks to me as you have customer IDs that are associated with more than one customer type in your data. 

[BrunPierre]

It appears so, how would you suggest  I work around it considering that every dimension selected distorts the distinct totals in the table? I'm considering disregarding any other dimension selection.

Please advise!

[Brett_Bleess]

@kush, @Vegar  Hey guys, I know you have been having issues with notifications, hopefully this one has what you need now.

Cheers,
Brett

[Kushal_Chawda]

@BrunPierre 

Would you be able to share the sample to look at?

[Kushal_Chawda]

Probably try using Dimensionality()

Assuming you have 3 dimensions in pivot table. If you have more then add more if conditions as described below. Basically simply when you see one dimension use aggr of that dimension and when you see two dimension use aggr of that two dimension and so on

if(Dimensionality()=1,

sum(aggr(COUNT({$<Year={'$(vMaxYear)'}>}DISTINCT CustomerIDToCount),Year)),

if(Dimensionality()=2,

sum(aggr(COUNT({$<Year={'$(vMaxYear)'}>}DISTINCT CustomerIDToCount),Year,Customer_Type)),

COUNT({$<Year={'$(vMaxYear)'}>}DISTINCT CustomerIDToCount)))