Fund value at a specfic date. Max Dates to be used for values for different products held by member.

[Ja123__]

Hi All,

I need assistance with planning a large calculation. Fund credits per Member per Product.

I will use 2 tables for my example:

Accounts (Client,Member, Ledger, Product, EffectiveDate, No.ofUnits)

UnitPrice (Product, Date, Price)

Although simplified, I want to start planning how I will be writing my set analysis. I want to determine no. of units x Last price that is less than the vEndDate. I have ordered my data to make use of first sorted value from the bottom. I have tested individual calculations that I learnt how to do off the forum previously.

Eg:

Last Rule for the Member

Aggr( {< dteEffectiveDate = {"<=$(vEndDate)"} >} FirstSortedValue(RuleID , - dteEffectiveDate ) , MemberID )

Last Price for the Product in my range

Aggr( {< dteDate16 = {"<=$(vEndDate)"} >} FirstSortedValue( numOfferPrice , - dteDate16 ) , InvestmentProductID )

But I'm a bit confused with aggregation when putting all the elements together? Could someone please give assistance?

Just to clarify. One client has many members. One member can have multiply funds. The fund will only be valued with the amount of units for the client before the vEndDate, valued at the last Unit Price before the vEndDate.  Let's say LedgerID = 100 for the relevant fund values.

I'll be filtering for 1 client at a time and then my table would ideally have ClientID,MemberID,Product, VALUE, which would then be totalled up to give the fund value for all the members for the client.

I think the example above will get me on my way before bringing in all the other filters. I included LedgerID as an example of all the other filters that I will need to build in. Other conditions I look at can be handled with IF statements with the amounts.

Regards,

James

[Anil_Babu_Samineni · Accepted solution]

Try this?

FirstSortedValue(RuleID , -Aggr(Only({<dteEffectiveDate = {"<=$(vEndDate)"}>} dteEffectiveDate), MemberID))

[Ja123__]

Thanks for the reply. Could you perhaps expand on this?
With ruleID aside, how would the calc look to calculate the following, which is what I want to determine:

Sum of all units, per product, per member, per client. Times by the maximum price by Product?

[Ja123__ · Accepted solution]

You have got me thinking about using set analysis in the sorted value calc.... With my basic example this seems to work?!

sum(
Aggr(

{<Ledger = {'100'}>}

sum( /*Can add more restriction for accounts table*/ Units) * FirstSortedValue({<Date = {"<=$(vToday)"}>} Price , - Date )

, Client, Member, Product)

)