aggr function in pivot table (not working for Null values)

[#1]

Hi,

I have a pivot table like this

CType	Status	Val	%
X	accept	5	14.3%
	reject	10	28.6%
	pending	20	57.1%
Status Sub total		35	100%
Y	accept	45	21.5%
	reject	40	19.0%
	pending	125	59.5%
Status Sub Total		210	100 %
-	accept	3	60%
	pending	1	20%
Status Sub Total		4	80%
TOTAL		250

CType and Status are the dimensions. Val and % are the expressions.

For the sub total calculatation I am using Partial Sum.

For Val, I am using

count(DISTINCT {$<EType={'SAS'}>}ENbr)

For the % calculation I am using following

Val/

aggr(nodistinct count({$<EType={'SAS''}>}ENbr),EType,CType)

When Ctype is NULL,the calculation becomes incorrect for % calculation.Though in the above example partial sum for CType where Null is 4 but in the % calculation the aggr function returns as 5,so the % calculation is wrong.Can anyone help on this?I am not sure why aggr function not working properly when Ctype is Null?

Second question,how to get the % value for TOTAL?

Thanks,Pooja

[swuehl]

Pooja,

your TOTAL returns 250 while the sum of your subtotals is 249, right? So maybe here is your missing count to '5', so maybe not the %, but the Val calculation is not correct?

Since I don't know anything about your data model, requirements etc. it's quite hard to say what you should or could do.

Hm, is it reasonably that you get CType NULL and do you want to show the Val, % then?

Have you tried using a total qualifier with subfields in the count instead of the aggr() for % expression?

[#3]

Opps..that's a Typo.Val is working fine.

Yes,that the requirement to show the Val and % when Ctype is Null.

I tried with Total qualifier,to calculate the %

Val/Sum(Total<Ctype>[Val]),but it is not working.Can you plz let me know whether anything wrong in the Total calc?

aggr(nodistinct count({$<EType={'SAS''}>}ENbr),EType,CType) and Sum(Total<Ctype>[Val]) is same thing or not?

[swuehl · Accepted solution]

Pooja,

I think they are not the same (depart from the missing set expression in the sum).

Have you tried

=Val / count({$<EType={'SAS'}>} DISTINCT TOTAL<CType> ENbr)

If this does not work, could you upload a small sample application or some inline tables data here?

[#5]

Thank you so much! it resolved all my issues.I really appreciated that you always reply back so quick with correct answer.