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.

[sunny_talwar]

Try this:

Count(TOTAL <Dim1> [FieldA])

[#3]

I knew i would get the right help here!

But, i realized that my question needs to be more accurate for the right help.

When i tried with your solution it gave me a (19+21)40 across all thru REMC and RMCSE.

Please find the table to provide more  idea on the post that i am looking to achieve.

Thanks.

[sunny_talwar]

Table? Did you attach something?

[#5]

Sorry,For some reason it didn't show up earlier, just re posted again.

[sunny_talwar]

I still think you can do this using this:

Count(TOTAL <Dim1> [FieldA])

Is this not working?

[#7]

With the Count(TOTAL <Dim1> [FieldA]),

I am getting a value 40 across Dim1-REMC and RMCSE.

But the anticipation is that it should be 19 for REMC across and 21 for RMCSE across.

Dim1	Dim2	Exp1
REMC	N/s 1c2	40
REMC	RWD	40
REMC	TESC	40
RMCSE	NS 2.1	40
RMCSE	RC 1.2	40
RMCSE	N/s 1c2	40

[sunny_talwar]

You have Both Dim1 and Dim2 as dimensions right and not just Dim2? If you have just Dim2 as dimension, then try this:

Sum(Aggr(Count(TOTAL <Dim1> [FieldA]), Dim1, Dim2))

[#9]

Hello Sunny,

There are Dim1 and Dim2 as dimensions in the pivot table and if try the expression created it does the aggr but again shows the aggr of overall Dim1 but need a dim1 values RMCSE and REMC.

looking for a which will show in the exp across for all Dim1 REMC it should 19 only same with the RMCSE it should be 21 static for all Dim2 values.

working on creating an example qvw that i can post for more info.

Thanks.

[sunny_talwar]

For demonstration created a sample. Check it out:

NOTE: Used Sum instead of Count, but you might need Count.