Pivot table, Aggr- help needed

[#1]

!Hello,

creating a pivot table with 2 dimensions (Dim1 and Dim2) and trying to create an expression where

it should be like

Aggr(count( Total Distinct [FieldA]),Dim1) - the aggr needs to be calculated on Dim1 only but the pivot table has Dim2 which shouldn't be included in calculating the expression.

Thanks.

[#2]

Hello Sunny,

Please find the qvw file with reduced data, on the original post.

The desired final end result should be below numbers irrespective of Dim2 in the pivot table.

But with your expression i was getting 3298 which the number 3298 should break out by Dim1 values as shown below.

ER	46
MR	1160
SR	520
SRM	797
WR	375

Thanks.

[sunny_talwar · Accepted solution]

This?

Count(DISTINCT TOTAL <Dim1> FAC_NUM)

[#4]

Well, all along i was missing to move the distinct and total around to get the required solution.

At the end i wouldn't need a Aggr after all .

Greatly appreciate your help.

Thanks.

[sunny_talwar]

It was not that: Here is the comparison:

Yours:

Count(TOTAL{<Dim1>} DISTINCT FAC_NUM)

Mine:

Count(DISTINCT TOTAL <Dim1> FAC_NUM)

or

Count(TOTAL <Dim1> DISTINCT FAC_NUM)

One is set analysis to ignore selection in Dim1 (Yours). Other is a field on which you want to TOTAL on (Mine)

[#6]

Thanks for the clarification Sunny.

[sunny_talwar]

No problem at all. I am glad I was able to offer help.

Best,

Sunny