topic Re: Aggregation or something like it? in QlikView

Aggregation or something like it?

Anonymous — Tue, 09 Apr 2013 11:49:09 GMT

I'm trying to do something fairly straightforward, but have become stuck.

Given a selection of Funds and Periods for a specific Date, I want the total number of Funds in each Category that the Fund belongs to (for each Period) so that I can calculate whether the Fund is within a certain quartile.

For clarity, there are about 31 categories, and each has multiple funds. I am only interested in some of those funds, hence the selection, but the Count of all the funds in the category corresponding to each of the selected funds is important.

I think the problem is related to me selecting the funds of interest, which excludes all the other funds.

You will see that the first Pivot has the Rank of the Funds selected, but this is from my data and is not calculated.

Please mess with the second Pivot to get me the required result, for example, STANLIB MM Balanced A should be 69 out of 111 for 3 Months. I need the 111, so that when I divide 69 by it, I can calculated that it is in the bottom half.

Thank you for any help.

Joao.

Re: Aggregation or something like it?

swuehl — Tue, 09 Apr 2013 12:57:20 GMT

Maybe like attached?

Re: Aggregation or something like it?

Anonymous — Tue, 09 Apr 2013 13:30:34 GMT

Ok, I've had partial success (see below), by doing the following:

Rank/aggr(max({1} Rank),ASISACategory,Period,ReturnDate)

Unfortunately, because some of the Funds below belong to the same ASISACategory, it only works for the first Fund in each category.

Does anyone know how to resolve this?

FundName	Period	3 Month	6 Month	1 Year	3 Years	5 Years
STANLIB MM Absolute Income A		0.80	0.82	0.60
STANLIB MM All Stars Eq FoF A		0.20	0.29	0.27	0.20	0.20
STANLIB MM Balanced A		0.62	0.59	0.45	0.37	0.58
STANLIB MM Equity A1
STANLIB MM Flex Property A		0.42	0.75	0.04	0.13	0.06
STANLIB MM Global Equity FF R		0.80	0.71	0.30	0.55	0.65
STANLIB MM Inflation Plus 1 FoF A		0.79	0.89	0.86	0.91
STANLIB MM Inflation Plus 3 FoF A
STANLIB MM Inflation Plus 5 FoF A
STANLIB MM Low Equity FoF A		0.64	0.64	0.64	0.64	0.50
STANLIB MM Med Equity FoF A						0.35
STANLIB MM Property A		0.65	0.76	0.70	0.33	0.41
STANLIB MM Real Return A

Re: Aggregation or something like it?

Anonymous — Mon, 15 Apr 2013 07:30:01 GMT

Hi Swuehl

This is not what I was looking for. Apologies for the poor explanation.

I've ended up doing it in a script in my database instead.

Thanks.

Joao.