8 Replies Latest reply: Nov 6, 2013 1:04 PM by Ingo Hüser RSS

    min of date of renewed contracts

    Ingo Hüser

      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?

        • Re: min of date of renewed contracts
          Stefan Wühl

          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.

          • Re: min of date of renewed contracts
            Ingo Hüser

            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 :-(

            • Re: min of date of renewed contracts
              Ingo Hüser

              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.

              • Re: min of date of renewed contracts
                Ingo Hüser

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

                  • Re: Re: min of date of renewed contracts

                    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

                      • Re: Re: min of date of renewed contracts

                        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

                         

                         

                         

                         

                         

                         

                    • Re: min of date of renewed contracts
                      Ingo Hüser

                      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.