topic Re: sum in QlikView

sum

upaliwije — Mon, 04 May 2015 11:43:01 GMT

I have attached a QV document with two straight table

Claim_No	count(DISTINCT Claim_No)
1	1
2	1
3	1
4	1
5	1
6	1
7	1
9	1
	8

Range	count(DISTINCT Claim_No)
200-300	2
300-400	2
400-500	2
100-200	3
>500	5
	14

In one table total count is 8 where as in the it is 14 against range. I want to show show total as 8 in Range table also. pls help me

Re: sum

buzzy996 — Mon, 04 May 2015 11:54:40 GMT

i think ur expecting total as 17, just remove distinct on ur range table expression and see?

Re: sum

Anonymous — Mon, 04 May 2015 11:58:58 GMT

in qvw you can already see total as 8

as your claim ID falling in different rang thats what they counted twice in list.

Thanks

BKC

Re: sum

upaliwije — Mon, 04 May 2015 12:03:59 GMT

wHAT i WANT IS 8 AS THE ANSWER EVEN AGAINST RANGE. IN ANY CASE THERE ARE ONLY 8 CLAIM NOS

Re: sum

buzzy996 — Mon, 04 May 2015 12:07:42 GMT

ur range table already showing as 8,wht else ur looking for?

Re: sum

upaliwije — Mon, 04 May 2015 12:15:54 GMT

there are only 8 claims . therefore first i want to group them by claim no and then apply range

Re: sum

sunny_talwar — Mon, 04 May 2015 12:18:42 GMT

by group by you mean you want to add all the amount against a specific claim_no and then give it a range? For instance claim_no 1 has amounts: 100,300,400 -> you want to add them up (which is 800) and then assign it to the range >500??

Is that what you are trying to do?

Best,

Sunny

Re: sum

upaliwije — Mon, 04 May 2015 12:26:35 GMT

Yes that is exactly what I want

Re: sum

sunny_talwar — Mon, 04 May 2015 12:30:33 GMT

‌there is two ways you can do that, either in the script or the front end... Do you prefer one or the other?

Re: sum

Anonymous — Mon, 04 May 2015 12:31:23 GMT

Use this

Re: sum

upaliwije — Mon, 04 May 2015 12:34:12 GMT

I like to know both ways

Re: sum

sunny_talwar — Mon, 04 May 2015 12:38:48 GMT

Script method using group by statement:

claim:

load * inline [

Claim_No, Amount,

1 ,100

2 ,200

3 ,300

4 ,400

5 ,500

6 ,600

7 ,100

9 ,200

1 ,300

1 ,400

2 ,500

3 ,600

4 ,400

4 ,800

4 ,100

5 ,500

6 ,700

];

Join(claim)

LOAD Claim_No,

Sum(Amount) as TotalAmt

Resident claim

Group By Claim_No;

Join(claim)

load

Claim_No,

Amount,

if(TotalAmt>=0 and TotalAmt<100, '0-100',

if(TotalAmt>=100 and TotalAmt<200, '100-200',

if(TotalAmt>=200 and TotalAmt<300, '200-300',

if(TotalAmt>=300 and TotalAmt<400, '300-400',

if(TotalAmt>=400 and TotalAmt<500, '400-500',

if(TotalAmt>=500 , '>500'))))))as Range

resident claim;

Re: sum

sunny_talwar — Mon, 04 May 2015 12:45:51 GMT

Here is how you do this in the front end (PFA)

HTH

Best,

Sunny

Re: sum

upaliwije — Mon, 04 May 2015 12:53:30 GMT

Thanks a lot for all

Re: sum

sunny_talwar — Mon, 04 May 2015 12:54:28 GMT

Not a problem.

Best,

Sunny

Re: sum

upaliwije — Mon, 04 May 2015 14:00:50 GMT

Can You please tell me how to assign a name for calculated dimension

Re: sum

sunny_talwar — Mon, 04 May 2015 14:20:48 GMT

Look at the image:

HTH

Best,

Sunny

Re: sum

upaliwije — Mon, 04 May 2015 14:29:42 GMT

Tks