topic Re: Aggregation based on maximum value of record in QlikView

Aggregation based on maximum value of record

Tue, 11 Dec 2012 14:20:01 GMT

Hi all,

I'm struggeling with this for a few days now so I hope one of you can help me out here.

I have a table like this:

ID	Code	Sort
1	R
1	V	1
2	R
2	E1	2
3	R
3	E2	2
3	V	1
4	R
4	E2	2
4	B	3

And I would like to have a chart in the front end counting the number of ID's by Code with only the records counted with the lowest sort value. So the result should be:

Code	Count
V	2
E1	1
E2	1
B	0
R	0

I've been using the firstsortvalue() function but as soon as I add Code as a dimension it doesn't work anymore.

All help is welcome!

Erik

Re: Aggregation based on maximum value of record

RedSky001 — Tue, 11 Dec 2012 15:15:06 GMT

How did you get a count of 1 for code E2, when the sort value is 2 for both rows? I would of thought the count should be 2... I'm confused (doesn't take much)

Re: Aggregation based on maximum value of record

Tue, 11 Dec 2012 16:29:18 GMT

Because the E2 for ID 3 has a sort value of 2 while V (of ID 3) has a sort value of 1. So only the smallest sort value should be counted.

Re: Aggregation based on maximum value of record

RedSky001 — Tue, 11 Dec 2012 17:21:56 GMT

Create a calculated dimension

=AGGR(if(Sort = Min(TOTAL <ID>Sort),Code),ID,Code)

then an expression:

count(ID)

B & R won't appear if the related sort values are nulls...

Re: Aggregation based on maximum value of record

Wed, 12 Dec 2012 14:21:33 GMT

That's it!

Thanks a lot for your help

Erik