Sum within a calculated expression of a PivotTable

[#1]

I have an expression within a Pivot Table that performs a count on a number of policies with , problem is there are several cases within these policies and this expression needs to check the overall value of these cases before it decides whether it can return this policy. Hard to explain but hopefully this example function will help:

COUNT (distinct

If(Sum([Policy Value]) > 100,[Policy Number])

)

As you can see if the value of the policy is greater than 100 I want to return the count of the policy number (also one on an individual policy level) but I have to use Sum() because the policy can appear more than once (4 cases within a policy for example).

An example of the table layout:

Policy No | Policy Value
1 | 100
1 | 80
1 | 70
2 | 200
3 | 100
3 | 250
3 | 10

The overall policy value for policy no 1 is 250...

[Anonymous]

You cannot use nested aggregation this way, sum inside count. Add aggr function, probably like this:

COUNT (distinct
aggr(If(Sum([Policy Value]) > 100,[Policy Number]), [Policy Number])
)

But it may depend on waht dimensions you're using in the pivot table.

Regards,
Michael

[sathishkumar_go]

Hey Try this,

(

if(sum([PolicyVALUE]) > 100,[PolicyNo])

[#4]

Cheers all for your responses, Michael that aggr did the trick appreciated!

Kev

[#5]

Hi guys,

I have a problem similar but I can't not solve it with your solution.

My dimension is a category of product (for which I put a target)

Count(distinct if ( InvoicedQty>Target and YYYYMM <= '200901' and YYYYMMlis >200801 , Customer))

but I want sum of InvoiceQTY

I tried:

Count(distinct aggr( if SUM(InvoicedQty) >Target and YYYYMM <= '200901' and YYYYMMlis >200801 , Customer),CATEGORY)

but it returns nothing.

Can you help me ?