How to sum based on dates

[#1]

Hello,

I have the need to sum a value based on the closest matching date to another.  I have a score that is pulled at a date and tied to a customer but not directly tied to a transaction.  The date the score is pulled is stored and the date the transaction executed is stored.  Since the score is not tied to the transaction and to the customer my data is not showing properly.  So I would like to put some set analysis in so that I only grab the score from the closest date prior to the execution date.

From the dummy data below I would only want to see the first row, I have tried a few things but no luck.  Any help would be much appreciated.

Trans ID	Score	ScoreDate	Execution Date	Amount
1234	450	3/31/2015	4/15/2015	200
1234	560	1/20/2015	4/15/2015	200

[sunny_talwar]

May be create a flag in the script and use the flag in the set analysis condition:

Table:

LOAD [Trans ID],

    Score,

    ScoreDate,

    [Execution Date],

    Amount,

    [Execution Date] - ScoreDate as Range

FROM

[https://community.qlik.com/thread/217587]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD Min(Range) as Range,

  1 as Flag

Resident Table;

You might need to Group by more dimensions, but the idea will remain the same

[#3]

That is a good idea but unfortunately the script is very massive and this is a one off request, I was hoping this was going to be simple and not require a lot of changes.

[sunny_talwar]

So you want to do this on the front end?

[#5]

Yes, that would be best.

[sunny_talwar]

May be using this set analysis:

{<ScoreDate = {"=([Execution Date] - ScoreDate) - (Min(TOTAL ([Execution Date] - ScoreDate))) = 0"}>}

[#7]

That didn't produce any results for me.  I actually just pulled the data that is duplicating out of QlikView and put it into excel and then created a new QlikView file to try your script option.  Unfortunately that only generated one Flag for 1 transaction and wasn't done for each transaction. 

How could we modify that so that it created a flag for each transaction?  If it did that then it would work perfect.

[sunny_talwar]

That's what I mentioned in my 1st response that you might need to Group by Trans ID or something else. Are you looking to get one combination of ScoreDate, Execution Date per Trans ID?

[sunny_talwar · Accepted solution]

Try with these slight modifications:

Table:

LOAD [Trans ID],

    Score,

    ScoreDate,

    [Execution Date],

    Amount,

    [Execution Date] - ScoreDate as Range

FROM

[https://community.qlik.com/thread/217587]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD [Trans ID],

  Min(Range) as Range,

  1 as Flag

Resident Table

Group by [Trans ID];

I should have just added this to my first post, apologize for not doing this earlier.

[#10]

That is very good.  Only one more thing.

How can we make it so the flag only generates on a positive value?