Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

Hi,

 

I have a pivot table like this

CTypeStatusVal%
Xaccept514.3%
reject1028.6%
pending2057.1%
Status Sub total35100%
Yaccept4521.5%
reject4019.0%
pending12559.5%
Status Sub Total210100 %
-accept360%
pending120%
Status Sub Total480%
TOTAL250

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

1 Solution

Accepted Solutions
MVP
MVP

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

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?

4 Replies
MVP
MVP

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

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?

Not applicable

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

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?

MVP
MVP

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

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?

Not applicable

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

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