Find a value based on an input date after 3 times aggregation

[#1]

I have a table with 3,21,34,981 rows. It is a transaction table with 8 fields. I have to create a summary report with 2 dimensions and 2 expressions based on an input date. The logic for obtaining data is, the data has to be fetched for the input date and if value not available for the date, it should fetch max date less than input date. The max date is found after three levels of aggregations. Sample data is attached.

As an example, I am giving input date, the value should be CUR_BALANCE aggregated over symbol_code, nin_code and account_number where the balance date <=input date.

Also please do suggest either back end loading or front end set analysis need to be done.

[#2]

Hi all,

I don't understand why no one is helping out/ suggesting a way out for the issue. Anyways after going through many articles in the community, i got a solution . But i need help in order to structure it. i have made a straight table with below information.

symbol_code,  investor, acct_number, balance_date, value

symbol_1          inv_1       acct_1        maxdate           value_1

symbol_1          inv_1       acct_2        maxdate           value_2

symbol_1          inv_2       acct_1        maxdate           value_3

i have used following as the expression.

firstsortedvalue(value,-aggr(max(balance_date),symbol_code,investor,acct_number,balance_date))

This exp gives me the value, but my report needs to be summarized for symbol_code only. Kindly help me out.