topic Re: Expression in QlikView

Expression

upaliwije — Sat, 05 Sep 2015 09:28:46 GMT

Hi Friends

Can someone pls correct my following expression

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

Re: Expression

sunny_talwar — Sat, 05 Sep 2015 09:31:33 GMT

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

Re: Expression

marcus_sommer — Sat, 05 Sep 2015 09:33:39 GMT

Try:

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

- Marcus

Re: Expression

upaliwije — Sat, 05 Sep 2015 09:34:28 GMT

Yes Both Premium and Amount

Re: Expression

upaliwije — Sat, 05 Sep 2015 09:41:36 GMT

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

Re: Expression

marcus_sommer — Sat, 05 Sep 2015 09:48:24 GMT

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

Re: Expression

upaliwije — Sat, 05 Sep 2015 09:51:21 GMT

Can u please write the expression

Re: Expression

marcus_sommer — Sat, 05 Sep 2015 09:59:01 GMT

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

Re: Expression

upaliwije — Sat, 05 Sep 2015 10:07:32 GMT

thanks

it is still returning figures for premium for all rows

Re: Expression

marcus_sommer — Sat, 05 Sep 2015 10:17:36 GMT

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))

Re: Expression

Anonymous — Sat, 05 Sep 2015 10:22:19 GMT

Hi,

try:

script:

if(len(trim(CLAIM_NO)) = 0, 0, CLAIM_NO) as CLAIM_NO

UI:

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

Re: Expression

upaliwije — Sat, 05 Sep 2015 10:23:15 GMT

Thanks for your help. But the result is the same. Can u pls think of condition with policy Period?

Re: Expression

PrashantSangle — Sat, 05 Sep 2015 10:32:41 GMT

Hi,

If possible post sample app, without it difficult to give answer.

Regards

Re: Expression

marcus_sommer — Sat, 05 Sep 2015 10:39:43 GMT

Have you checked if the result is the sum of PREMIUM or from AMOUNT then the expression returned in every case a sum-function. I suggest you put each part from the expression in a single expression-column to see what each returned and the to decide if this are the expected results and then put them together again.

- Marcus

Re: Expression

upaliwije — Sat, 05 Sep 2015 10:43:47 GMT

AMOUNT	CLAIM_NO	PAID_AMOUNT	DESC	POLICY_NO	POLICY_PERIOD	PREMIUM	RISK
	CLGN001A15000036	4000		GN00111A0000119	Aug-14 - Aug-15		17-6819
	CLGN001A15000023	12200		GN00111A0000119	Aug-14 - Aug-15		17-6819
	CLGN001A15000063	6500		GN00111A0000119	Aug-14 - Aug-15		17-6819
	CLGN001A15000063	6700		GN00111A0000119	Aug-14 - Aug-15		17-6819
	CLGN001A15000021	4500		GN00111A0000119	Aug-14 - Aug-15		17-6819
	CLGN001A15000021	12500		GN00111A0000119	Aug-14 - Aug-15		17-6819
1200			SRCC TO VEHICLE	GN00111A0000119	Aug-12 - Aug-13	14505	17-6819
1200			SRCC TO VEHICLE	GN00111A0000119	Aug-13 - Aug-14	13805	17-6819
1200			SRCC TO VEHICLE	GN00111A0000119	Aug-14 - Aug-15	13105	17-6819
1200			SRCC TO VEHICLE	GN00111A0000119	Aug-15 - Aug-16	15205	17-6819
1400			FLOOD COVER AND NATURAL PERILS	GN00111A0000119	Aug-12 - Aug-13	14505	17-6819
1400			FLOOD COVER AND NATURAL PERILS	GN00111A0000119	Aug-13 - Aug-14	13805	17-6819
1400			FLOOD COVER AND NATURAL PERILS	GN00111A0000119	Aug-14 - Aug-15	13105	17-6819
1400			FLOOD COVER AND NATURAL PERILS	GN00111A0000119	Aug-15 - Aug-16	15205	17-6819
-3500			NO CLAIM BONUS	GN00111A0000119	Aug-14 - Aug-15	13105	17-6819
-3500			NO CLAIM BONUS	GN00111A0000119	Aug-15 - Aug-16	15205	17-6819
14000			BASIC PREMIUM	GN00111A0000119	Aug-12 - Aug-13	14505	17-6819
14000			BASIC PREMIUM	GN00111A0000119	Aug-13 - Aug-14	13805	17-6819
14000			BASIC PREMIUM	GN00111A0000119	Aug-14 - Aug-15	13105	17-6819
14000			BASIC PREMIUM	GN00111A0000119	Aug-15 - Aug-16	15205	17-6819
5			ROAD SAFETY FUND	GN00111A0000119	Aug-12 - Aug-13	14505	17-6819
5			ROAD SAFETY FUND	GN00111A0000119	Aug-13 - Aug-14	13805	17-6819
5			ROAD SAFETY FUND	GN00111A0000119	Aug-14 - Aug-15	13105	17-6819
5			ROAD SAFETY FUND	GN00111A0000119	Aug-15 - Aug-16	15205	17-6819
0			FREE HEALTH COVER	GN00111A0000119	Aug-14 - Aug-15	13105	17-6819
0			FREE HEALTH COVER	GN00111A0000119	Aug-15 - Aug-16	15205	17-6819
2100			ADDITIONAL LOADING 1	GN00111A0000119	Aug-15 - Aug-16	15205	17-6819
-2800			NO CLAIM BONUS	GN00111A0000119	Aug-13 - Aug-14	13805	17-6819
-2100			NO CLAIM BONUS	GN00111A0000119	Aug-12 - Aug-13	14505	17-6819 !