Expression

[upaliwije]

Hi Friends

Can someone pls correct my following expression

sum(if(isnull(DESC) and Count(CLAIM_NO)>0,PREMIUM,AMOUNT))

[sunny_talwar]

The inner COUNT will need a aggregation function I think. Count(CLAIM_NO) over what dimension/s? Both PREMIUM and AMOUNT?

[marcus_sommer]

Try:

if(isnull(DESC) and Count(CLAIM_NO)>0, sum(PREMIUM), sum(AMOUNT))

- Marcus

[upaliwije]

Yes Both Premium and Amount

[upaliwije]

It is not working

My pivot table is as Follows

RISK	POLICY_PERIOD	POLICY_NO	CLAIM_NO	PAID_AMOUNT	PREMIUM
17-6819	Aug-13 - Aug-14	GN00111A0000119	-	0	13,805
17-6819	Aug-14 - Aug-15	GN00111A0000119	CLGN001A15000021	17,000	0
17-6819	Aug-14 - Aug-15	GN00111A0000119	CLGN001A15000023	12,200	0
17-6819	Aug-14 - Aug-15	GN00111A0000119	CLGN001A15000036	4,000	0
17-6819	Aug-14 - Aug-15	GN00111A0000119	CLGN001A15000063	13,200	0
17-6819	Aug-14 - Aug-15	GN00111A0000119	-	0	13,105
17-6819	Aug-15 - Aug-16	GN00111A0000119	-	0	15,205

I want Premium shown in Bold not to appear since No claim Ns are recorded against them

[marcus_sommer]

Your check contains an AND which meant that both conditions must be true. You could leave your isnull(Desc) or replace it with something like len(trim(DESC))>0. Also if you have CLAIM_NO as column within the pivot you could reference to this column per column-name or column-no instead of calculating the count(CLAIM_NO) again.

- Marcus

[upaliwije]

Can u please write the expression

[marcus_sommer]

Try:

assuming that CLAIM_NO is the first calculation-column:

if(len(trim(DESC))>0 and Column(1)>0, sum(PREMIUM), sum(AMOUNT))

if CLAIM_NO is a dimension:

if(len(trim(DESC))>0 and len(trim(CLAIM_NO))>0, sum(PREMIUM), sum(AMOUNT))

- Marcus

[upaliwije]

thanks

it is still returning figures for premium for all rows

[marcus_sommer]

Try it with only a single-condition:

if CLAIM_NO is a dimension:

if(len(trim(DESC))>0 and len(trim(CLAIM_NO))>0, sum(PREMIUM), sum(AMOUNT))