How can I apply a custom aggregation function on a table?

Table-valued functions in finaquant .net libraries

How can I apply a custom aggregation function on a table?

Postby ariston » 23 Oct 2013, 18:13

Hi again,
is it possible to apply a custom aggregation function on a data table? I mean an aggregation function other than the standard ones like addition, average...
thabnks
ariston
 
Posts: 6
Joined: 20 Oct 2013, 13:51

Table Aggregation Functions with custom aggregation function

Postby finaquant » 24 Oct 2013, 20:14

Yes, possible.

There are currently three different Table Functions for this purpose in our .net libraries finaquant® protos (non-commercial) or finaquant® calcs (commercial):

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. MatrixTable.AggregateAllKeyFigures_UserFunc()
  2. MatrixTable.AggregateSelectedKeyFigure_A_UserFunc()
  3. MatrixTable.AggregateSelectedKeyFigure_B_UserFunc()
GeSHi ©


See demo function named Table_aggregation_functions in Visual Studio file FinaquantProtosStarter or StarterFinaquantCalcsStarter that you can download at their respective product page (links above).
Image

C# code example:
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. //**********************************************************
  2. // aggregate all key figures with user defined functions
  3. //**********************************************************
  4.  
  5. // available standard delegate functions:
  6. // - Utility.Aggregate_Sum
  7. // - Utility.Aggregate_Avg
  8. // - Utility.Aggregate_Max
  9. // - Utility.Aggregate_Min
  10. // default aggregation function is Utility.Aggregate_Sum
  11.  
  12. MatrixTable.View_MatrixTable(tbl7, "Aggregate all key figures with user-defined function, table before aggregation");
  13.  
  14. // set user defined function for key figure costs
  15. var AggrFunc = new Dictionary<string, AggregateVector>();
  16. AggrFunc["costs"] = UserFunctions.PopulationVariance;
  17.  
  18. // aggregate all key figures
  19. tblr = MatrixTable.AggregateAllKeyFigures_UserFunc(tbl7, AggrFunc);
  20. MatrixTable.View_MatrixTable(tblr, "(1) Aggregate costs: Cost variance per product");
  21.  
  22. //**********************************************************
  23. // aggregate selected key figures with user defined function
  24. //**********************************************************
  25.  
  26. // aggregate "costs" w.r.t. all attributes: category & product
  27. tblr = MatrixTable.AggregateSelectedKeyFigure_A_UserFunc(tbl7,
  28.     InputKeyFigName: "costs", OutputKeyFigName: "cost_variation",
  29.     AggrFunc: UserFunctions.PopulationVariance);
  30.  
  31. MatrixTable.View_MatrixTable(tblr, "(2) Aggregate costs: Cost variance per product");
  32.  
  33. //**********************************************************
  34. // aggregate selected key figures w.r.t. selected reference attributes
  35. // with user defined function
  36. //**********************************************************
  37.  
  38. // aggregate "costs" w.r.t. selected attributes: category & product
  39. // i.e. ignore attribute year for aggregation
  40. MatrixTable.View_MatrixTable(tbl5, "(3) Aggregate costs: Cost variance per product - before aggregation");
  41.  
  42. tblr = MatrixTable.AggregateSelectedKeyFigure_B_UserFunc(tbl5,
  43.     TextVector.CreateVectorWithElements("category", "product"),
  44.     InputKeyFigName: "costs", OutputKeyFigName: "cost_variation",
  45.     AggrFunc: UserFunctions.PopulationVariance);
  46.  
  47. MatrixTable.View_MatrixTable(tblr, "(3) Aggregate costs: Cost variance per product - after aggregation");
GeSHi ©
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37

Re: How can I apply a custom aggregation function on a table

Postby ariston » 25 Oct 2013, 11:26

thanks. is there an example for the custom aggregation function? what sort of functions is PopulationVariance that you use in your example?
ariston
 
Posts: 6
Joined: 20 Oct 2013, 13:51

Population Variance as custom aggregation function

Postby finaquant » 27 Oct 2013, 16:55

The custom aggregation function UserFunctions.PopulationVariance is a static method defined in code file UserFunc.cs in Visual Studio file FinaquantCalcsStarter (see related downloads).

This is an example user-defined function which takes a vector as the single input parameter, and returns a scalar of type double.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. /// <summary>
  2. /// Calculate population variance
  3. /// </summary>
  4. /// <param name="V">Input vector</param>
  5. /// <returns>Population variance</returns>
  6. public static double PopulationVariance(KeyVector V)
  7. {
  8.     // check if vector is null or empty
  9.     if (V == null || V.IsEmpty)
  10.         throw new Exception("UserFunctions.PopulationVariance: Null or empty input vector!\n");
  11.  
  12.     // calculate mean (average value)
  13.     Double Mean = V.toArray.Average();
  14.  
  15.     // calculate population variance
  16.     Double VarSum = 0.0;
  17.  
  18.     for (int i = 0; i < V.nLength; i++)
  19.     {
  20.         VarSum = VarSum + Math.Pow(V[i] - Mean, 2.0);
  21.     }
  22.     return VarSum / V.nLength;
  23. }
GeSHi ©
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37

Re: How can I apply a custom aggregation function on a table

Postby ariston » 29 Oct 2013, 20:52

many thanks!
ariston
 
Posts: 6
Joined: 20 Oct 2013, 13:51


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest

cron