topic Show max count per dimension in QlikView

Show max count per dimension

Thu, 10 Mar 2011 15:22:32 GMT

I'm trying to find the max count per dimension. My table looks like this - simplified:

ID	Value1	Value2	Value3
1	55	6	78
2	1	152	0
3	65	0	2

So for ID 1 I should see 78 which is in Value3.

For ID 2 I should see 152 which is in Value2 and for ID 3 I should see Value 65 which is in Value1.

All the figures in Value* are count(key) and the Value* comes from a field called ValueType.

I can't do this in load script.

I have tried

aggr(max(aggr(count(Key), ValueType, ID)), ID)

which sort of work but returns the wrong Value name in some cases and I have to be able to select e.g. Value1 and only the IDs that have max count in that Value should be displayed.

Any ideas ?

Show max count per dimension

Oleg_Troyansky — Thu, 10 Mar 2011 15:31:01 GMT

If you use ID as your chart Dimension, than the second aggr is unnecessary - QlikView will aggregate the expression by your Dimension values. So, with ID as a Dimension, the expression could look like this:

max(aggr(count(Key), ValueType, ID))

Show max count per dimension

Thu, 10 Mar 2011 15:55:14 GMT

Thanks for the tip but that still doesn't solve my problem of selecting ValueType. If I eg select Value1 I want to see only those that has max number of IDs in that Value.

Edit: max number of keys in that Value