Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be this
Sum(Aggr(
If(Last_reported_date = Max(TOTAL <ACC_ID> Last_reported_date), Sum(TOTAL_AMOUNT))
, ACC_ID, Last_reported_date))
May be this
Sum(Aggr(
If(Last_reported_date = Max(TOTAL <ACC_ID> Last_reported_date), Sum(TOTAL_AMOUNT))
, ACC_ID, Last_reported_date))
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
Post some sample data with expected output. This will help to get faster reply
Not really sure I follow... like @Kushal_Chawda said, would you be able to share a sample to understand your issue better?
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.
Thanks guys the solution that you provided works well.