Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

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.

 

1 Solution

Accepted Solutions
Highlighted

Re: Last_Reported date or MAX of Field for each ID as of specific Upload_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))

View solution in original post

6 Replies
Highlighted

Re: Last_Reported date or MAX of Field for each ID as of specific Upload_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))

View solution in original post

Highlighted
Partner
Partner

Re: Last_Reported date or MAX of Field for each ID as of specific Upload_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

Highlighted

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

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

Highlighted

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

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

Highlighted
Partner
Partner

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

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.

Partner
Partner

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

Thanks guys the solution that you provided works well.