Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends
Can someone pls correct my following expression
sum(if(isnull(DESC) and Count(CLAIM_NO)>0,PREMIUM,AMOUNT))
The inner COUNT will need a aggregation function I think. Count(CLAIM_NO) over what dimension/s? Both PREMIUM and AMOUNT?
Try:
if(isnull(DESC) and Count(CLAIM_NO)>0, sum(PREMIUM), sum(AMOUNT))
- Marcus
Yes Both Premium and Amount
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
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
Can u please write the expression
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
thanks
it is still returning figures for premium for all rows
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))