6 Replies Latest reply: Mar 18, 2016 10:24 AM by Eric Hannert RSS

    Differenc between dates

    Eric Hannert

      Hi community,


      i want the difference of to dates with different values in a other coloum.

      so for excample i have


      article     info     date

      apple     start     1/2/2012

      cheese   start     3/5/2013    

      cheese     end     6/7/2013    

      apple     end     12/2/2016


      and i want the difference between apple start and end.


      So i have the following command for the date differenz:



      but i dont know how to say i want it for apple or cheese.


      Thx for help

        • Re: Differenc between dates
          Marcus Sommer

          You could do it within a sorted resident load per Peek() or Previous() ? or you joined both dates together like:



          Load article, date as startdate From xyz where info = 'start';

               left join

          Load article, date as enddate From xyz where info = 'end'



          Load article, startdate, enddate, enddate - startdate as diffdate resident temp;

          drop table temp;


          - Marcus

            • Re: Differenc between dates
              Eric Hannert

              sorry i delete all answers my fault.


              but now i have this error:


              Der folgende Fehler ist aufgetreten:

              Garbage after statement

              Der Fehler ist hier aufgetreten:

              temp: NOCONCATENATE load Artikelnummer,Datum as Wareneingang_datum where "Bezeichnung Buchungsart" ='Wareneingang' Resident InitialLoad

                • Re: Differenc between dates
                  Marcus Sommer

                  Sorry, the source (resident) must be of course before the condition (where):



                  NOCONCATENATE load

                       Artikelnummer,Datum as Wareneingang_datum

                  Resident InitialLoad

                  where "Bezeichnung Buchungsart" ='Wareneingang';


                  - Marcus

                    • Re: Differenc between dates
                      Eric Hannert

                      Hi Marcus,


                      i run around in my data and had another idea for something i can display.

                      What about a KPI showing the timerange in days an article was in store from delivery to the store till it was


                      So we have articlenumber, amount,sold/delivery, date.

                      The thing is that there can be 2 deliveries of a product before even one sales.

                      But with diffdate i only get the datedifferenc to the last deliverie of the same product.

                      So maybe create a var with the amount coming in from delivery and use this as the delivery date for the diffdate expression as long its not 0.And when its 0 switch to the next delivery date, so we allways have the right date.

                      And an if  there is no delivery date before the sold-date i want to take the start date of the timerange.


                      The problem here, again i have a great idea but no clue about how i can achiev this in the right way.

                      So maybe u can give me some tipps.


                      Thanks for help in advance


                • Re: Differenc between dates
                  Sunny Talwar

                  May be this:



                  LOAD * Inline [

                  article,     info,     date

                  apple,     start,     1/2/2012

                  cheese,   start,     3/5/2013   

                  cheese,     end,     6/7/2013   

                  apple,     end,     12/2/2016




                  LOAD article,



                    If(article = Peek('article'), Interval(date - Peek('date'), 'D')) as Difference

                  Resident Table

                  Order By article, date;


                  DROP Table Table;