Aggr() function from QV12 to QV11.2

[Anonymous]

I had been developing an application in QV12 in which one of the key functions rely on the use of the QV12 sortable aggr() function. I use the aggr() function to sum up market values and cashflow for each dat in the dataset, grouped by diferent aspects of the data, and then calculate the performance on each of these aspects of the data by fetching the previos days summed market value.

Long story short, it turns out the server that will be hosting the application runs QV11.2, not QV12 and in QV11.2 the aggr() function is not sortable, hence I'm not able to fetch the previous dates market value, it just fetches a random dates market value. The performance calculated is hence nonsensical.

My question is, and I'm guessing the answer to it is no; Is there any way to code, or copy the code, for the aggr() function in QV12 and implement it as a macro in QV11.2 so that I'm able to salvage my app withouth a total rebuild?

Hope someone can help or at least confirm my suspicion that it is not possible, so that I can move on...

[marcus_sommer]

I don't think that this is possible and it's in general not recommended to mix up different releases between dev/prod environments than beside missing new features there is always a risk of any incompatibility. This meant you should consider an up- or a downgrade of your environments.

- Marcus

[petter]

Even though Aggr() in previous versions can't sort itself you can depending on the context use Concat( Aggr( .....) , '|' , <sortfield> ) and then pick the one you want or whatever.... it might be convoluted and not very efficient but that depends on the situation really...

You should also be aware of that many developers use Aggr() when simpler and more efficient methods could be used. Aggr() is often overused .... are you absolutely sure you have to use Aggr() at all?

If you explain how you have used Aggr() may someone could suggest an alternative way of calculating the same?

[petter]

Just a quick example using Concat to sort.... Of course you have some unpacking to do from the resulting string:

But for example:

TextBetween( v2 , 'B:' , '|') will pick out value associated with the B dimension

You might not need to put in the dimension values in the result string at all making it easier to find min max and so forth...

[Anonymous]

I think i will need to use the aggr, function, but please feel free to prove me wrong. Unfortunatley I can not share my application with you due to work restrctions, but I'll try to explain what I'm trying to do.

A simplified version of my data looks like this:

Date	Portfolio	Sub portfolio	Asset class	Sub asset class	Security	Market value	Cashflow
01.01.2010	A	AA	Equity	Domestic	1	100	0
01.01.2010	A	AB	Bond	Global	2	50	0
01.01.2010	B	BA	Cash	Bank account	3	100	0
02.01.2010	A	AA	Equity	Domestic	1	160	-50
02.01.2010	A	AB	Bond	Global	2	45	0
02.01.2010	B	AA	Cash	Bank account	3	50	50
03.01.2010	A	AA	Equity	Domestic	1	150	0
03.01.2010	A	AA	Equity	Global	4	50	-50
03.01.2010	A	AB	Bond	Global	2	50	0
03.01.2010	B	BA	Cash	Bank account	3	0	50

The user  selects a date range and then i have a pivot table where the end user can expand or collapse the other dimensions and thereby look at the data grouped in different levels. The aggregate function is used to make a virtual table as follows (when the user expands e.g. portfolio.

Date	Portfolio	SUM(Market Value)	Above(SUM(Market Value))	SUM(Cashflow)
01.01.2010	A	150	[NULL]	0
02.01.2010	A	205	150	-50
03.01.2010	A	250	205	-50
01.01.2010	B	100	[NULL]	0
02.021.2010	B	50	100	50
03.01.2010	B	0	50	50

Which will be used to calculate the following result pivot that is presented to the end user:

Portfolio	exp(sum(1+log(Aggr((SUM(Market value)-ABOVE(SUM(Market value))+SUM(Cashflow))/ABOVE(SUM(Market value)),Date,Portfolio))))-1
A	0,81%
B	0,00%

And withouth the sorting it is the above() part that is not working as that does not get the "above" value sorted by date but it seems to be rather arbitrary how it is sorted.

As for your concat suggestion I do not see exactly how that would work, or if it would work for my situation, but I will spend some time trying to understand it and see if I can make use of it.