Last_Reported date or MAX of Field for each ID as of specific Upload_Date

[dewan_abdullah]

Hello everyone,

I'm having some difficulties with retrieving the latest record for each account id for any specific date based on the selection. my table has the following fields (along with another 170 fields):

ACC_ID, FILE_ID,Last_Reported_Date, UPLOAD_DATE, TOTAL_AMOUNT

FileID is unique everyday(UPLOAD_DATE) but has multiple ACC_ID's and last_Reported_Date's

I'm trying to build a KPI chart that has the total sum of TOTAL_AMOUNT for each ACC_ID where fileID is Maximum for that specific upload_Date

I tried this Sum(TOTAL_AMOUNT* (if(FILE_KEY=AGGR(MAX(FILE_KEY),ACC_ID),1,0))

but it's not giving the right output,

I can use the max(File_key) or max(Last_reported_date) it should give the exact output.

[sunny_talwar · Accepted solution]

May be this

Sum(Aggr(
    If(Last_reported_date = Max(TOTAL <ACC_ID> Last_reported_date), Sum(TOTAL_AMOUNT))
, ACC_ID, Last_reported_date))

[dewan_abdullah]

It didn't work,

actually from database side they use Rank function (after sorting based on last_reported_dt) to get the first value for each acc_ID for a specific upload_date for example (Upload_date <'01/12/2019') but i dont want to go this way, because the user wants to change the upload date from the filter not from the script. 

so is there any other possible way?

Thanks

[Kushal_Chawda]

Post some sample data with expected output. This will help to get faster reply

[sunny_talwar]

Not really sure I follow... like @Kushal_Chawda said, would you be able to share a sample to understand your issue better?

[dewan_abdullah]

Sorry for the late reply,

Actually there is another condition that I forgot to mention:

the account ,while taking the Max reported date, should have a value for cf_closing_key=-1

something similar to this:

Sum(Aggr(
If(Last_reported_date = Max(TOTAL <ACC_ID> Last_reported_date, IF(CF_CLOSING_KEY=-1)), Sum(TOTAL_AMOUNT))
, ACC_ID, Last_reported_date))

but the result is 0.

[dewan_abdullah]

Thanks guys the solution that you provided works well.