Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nlpanders
Contributor II
Contributor II

Aggr() function from QV12 to QV11.2

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...

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Partner
Partner

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
Partner
Partner

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

2017-02-20 20_26_17-QlikView x64 - [C__Users_psd_Downloads_Calculated Dimensions.qvw_].png

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...

nlpanders
Contributor II
Contributor II
Author

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:

DatePortfolioSub portfolioAsset classSub asset classSecurityMarket valueCashflow
01.01.2010AAAEquityDomestic11000
01.01.2010AABBondGlobal2500
01.01.2010BBACashBank account31000
02.01.2010AAAEquityDomestic1160-50
02.01.2010AABBondGlobal2450
02.01.2010BAACashBank account35050
03.01.2010AAAEquityDomestic11500
03.01.2010AAAEquityGlobal450-50
03.01.2010AABBondGlobal2500
03.01.2010BBACashBank account3050

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.

DatePortfolioSUM(Market Value)Above(SUM(Market Value))SUM(Cashflow)
01.01.2010A150[NULL]0
02.01.2010A205150-50
03.01.2010A250205-50
01.01.2010B100[NULL]0
02.021.2010B5010050
03.01.2010B05050

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

Portfolioexp(sum(1+log(Aggr((SUM(Market value)-ABOVE(SUM(Market value))+SUM(Cashflow))/ABOVE(SUM(Market value)),Date,Portfolio))))-1
A0,81%
B0,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.