topic Re: How to sum based on dates in QlikView

How to sum based on dates

Thu, 19 May 2016 17:46:33 GMT

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

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 17:51:26 GMT

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

Re: How to sum based on dates

Thu, 19 May 2016 17:57:17 GMT

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.

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 17:58:35 GMT

So you want to do this on the front end?

Re: How to sum based on dates

Thu, 19 May 2016 17:59:26 GMT

Yes, that would be best.

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 18:44:33 GMT

May be using this set analysis:

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

Re: How to sum based on dates

Thu, 19 May 2016 19:14:50 GMT

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.

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 19:17:52 GMT

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?

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 19:19:43 GMT

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.

Re: How to sum based on dates

Thu, 19 May 2016 19:25:31 GMT

That is very good. Only one more thing.

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

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 19:27:20 GMT

May be this:

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,

If(Min(Range) > 0, 1) as Flag

Resident Table

Group by [Trans ID];

Re: How to sum based on dates

Thu, 19 May 2016 19:34:35 GMT

That is close but if the value for the Transaction is less than 0 I would want to take the lowest positive value and this doesn't mark any flags for that transaction.

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 19:36:11 GMT

When you say value, do you mean Score or Amount?

Re: How to sum based on dates

Thu, 19 May 2016 19:47:31 GMT

I meant the Range. I actually was able to complete it.

Left Join (Table)

LOAD [Trans ID],

Min(Range) as Range,

1 as Flag

Resident Table

Where Range >0

Group by [Trans ID];

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 19:48:54 GMT

Great

Re: How to sum based on dates

Thu, 19 May 2016 19:50:24 GMT

Thank you very much for your help.

Re: How to sum based on dates

sunny_talwar — Thu, 19 May 2016 19:55:21 GMT

No problem at all. I am glad I was able to help