min of date of renewed contracts

[ingoniclas]

I have a table with contract relationsships and a field with each month for which the contract ist valid, respectively. See an example below showing just one particluar relationship where a contract is valid from Jan 2013 till Mar 2013, and was renewed in Jun 2013:

partner A          partner B     date

x                     y                 01/01/2013

x                     y                 02/01/2013

x                     y                 03/01/2013

x                     y                 06/01/2013

x                     y                 07/01/2013

The challenge: I want to select the min(date) of the latest contract relationsship, i.e. in this example I want min(date) to be Jun 2013, NOT Jan 2013. Any ideals how I could realize this either in script or chart?

[swuehl · Accepted solution]

If you want to do it in the script (and that's probably advisable, if the results are not depending on selections),

you can do it like this (just as a start, you don't need to drop all your source data, but then you need to rename fields / create appr. keys):

Set DateFormat = 'MM/DD/YYYY';

INPUT:

LOAD * INLINE [

partner A,          partner B,     date

x,                     y,                 01/01/2013

x,                     y,                 02/01/2013

x,                     y,                 03/01/2013

x,                     y,                 06/01/2013

x,                     y,                 07/01/2013

];

BLOCKS:

LOAD *,

    if(peek([partner A]) = [partner A]

        and peek([partner B]) = [partner B]

        and date-peek(date) <32,

    peek(Block),

    rangesum(peek(Block),1)) as Block

Resident INPUT order by [partner A], [partner B], date asc;

drop table INPUT;

RESULT:

LOAD [partner A], [partner B], Block,

     date(max(date)) as EndDate,

     date(min(date)) as StartDate

Resident BLOCKS group by [partner A], [partner B], Block;

drop table BLOCKS;

In a chart with dimensions partner A and partner B, max(StartDate) will give you the start of the latest contract period.

[ingoniclas]

Many thanks for your reply, however, I am afraid it did not work. The script ran through flawlessly, but the result was the same as without your snippet 😞

[swuehl]

This is strange. The result can't be the same, because I am dropping the original tables and creating a different table with different and new columns.

[ingoniclas]

You are right, I could reproduce your result using the example data. The issue must be in the context of my script. I will have to investigate the error. Many thanks.

[ingoniclas]

I finally found and eliminated the error with my real data, now it works. Many thanks!

[#7]

Hi;

I hope that can help you!!!!

Check this .qvw

Mmmm for example if you have differente dates:

01/01/2013 up to 07/01/2013

what you need is the penultimate (penultimo) row ???

if this is correct I attached a .qvw that can help.

regardsss!!! and is very sample!!!   JUST CHECK the Function:    Peek

[#8]

Sorry I forgot:

the peek() :

(name_colum, number of row, name_table)

(sales,0,'inputtable') as peek 

the number 0 mean that will take the first row, for example

sales

10

20

30

40

50

then my result is      10

(sales,1,'inputtable') as peek

then my result is      20

but if EVER I want the  penultimate (penultimo) row then use negatives

(sales,-2,'inputtable') as peek

then my result is      40

[ingoniclas]

I have an additional challenge here: I deliver product A to retail stores. Each retail store gets a 10% rebate back from me for every product sold. However, they retail stores sometimes confuse my product A with product B from another company and incorrectly claim rebates from me. In order to see which claims are incorrect I want to make use of the fact that my product A is only good for 90 days after delivery. In order to check on that I want to link my table with the records of my deliveries with each retailers' sales report via the product ID and automatically identify sales made >90 days after my last delivery. How can I achieve this? See an example below:

table with my deliveries to the wholesaler:

Product ID        Delivery date          Price

A                     01/01/2012             20 $

A                     01/05/2012             10 $

report of the wholesaler to claim his rebates:

Product ID        Sales date          Claimed rebate

A                     02/15/2012              2 $                       -> correct

A                     04/10/2012              2 $                       -> incorrect, no rebate to be disbursed since it was sold > 90 days after my last delivery. It can't be my product A.

Many thanks for any help.