Summarize Values by For Loop (Table Output)

maddin-182-
edited February 11, 2022 in Analytics #1
<p>Hi all,</p>
<p> </p>
<p>my DataSet is a sql query (postgres DB). </p>
<p>There a two columns  in table. First column is 'order', second 'creation_time'.</p>
<p>There are different rows with the same value for 'order'.</p>
<p> </p>
<p>My intention is to summarize all values from 'creation_time' for each order + the value of the first row of the next order.</p>
<p> </p>
<p>There should be an output in a table with a third column 'sum'.</p>
<p>Might there is a possibility to create a new data field an execute an foor loop in javascript within (loop for each order + 1 ..) </p>
<p> </p>
<p>I´m very grateful for every helpful idea.</p>
<p> </p>
<p>Thanking you in advance.</p>
<p> </p>
<p>Best regards,</p>
<p>Martin</p>

Comments

  • <p>Hi Martin,</p>
    <p>  So your result set looks like this?</p>
    <pre class="_prettyXprint _lang-">
    Order Number | Creation Time
    ______________|__________________
    0001 | 07/30/2014
    0001 | 07/31/2014
    0001 | 08/01/2014
    0002 | 07/30/2014
    0002 | 07/31/2014
    0002 | 08/01/2014
    0003 | 07/30/2014
    0003 | 07/31/2014
    0003 | 08/01/2014
    </pre>
    <p>And you'd like to produce a report with a table that is similar to the following?</p>
    <pre class="_prettyXprint _lang-">
    0001 | |
    | 07/30/2014 | 3
    | 07/31/2014 | 3
    | 08/01/2014 | 3
    | |
    | Total for 0001 | 9
    0002 | |
    | 07/30/2014 | 3
    | 07/31/2014 | 3
    | 08/01/2014 | 3
    | |
    | Total for 0002 | 9
    0003 | |
    | 07/30/2014 | 3
    | 07/31/2014 | 3
    | 08/01/2014 | 3
    | |
    | Total for 0003 | 9
    </pre>
    <p>Is the above similar to the table your attempting to create?  Can you explain what you mean by loop for each order + 1?</p>
    Warning No formatter is installed for the format ipb
  • <div><span style="font-family:'courier new', courier, monospace;">Hi Kristopher,</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">sorry, i think in there were not enough information in my first post. Let me explain with following table:</span></div>
    <div> </div>
    <div><span style="font-family:'courier new', courier, monospace;">Order Number  |  Start Time     |    End Time     | Diff in minutes</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">______________|_________________|_________________|</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0001          |07/30/2014 07:30 | 07/30/2014 07:33| 3</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0001          |07/30/2014 07:33 | 07/30/2014 07:38| 5</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0001          |07/30/2014 07:38 | 07/30/2014 07:55|17</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">                                                  |35 (Diff-Time last Endtime order 0001 and first                                                           |       starttime next order 0002)</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">                                                  |60 (Total for order 0001)</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0002          |07/30/2014 08:30 | 07/30/2014 08:36| 6</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0002          |07/30/2014 08:36 | 07/30/2014 08:39 ...</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0002          |07/30/2014 08:39 | 07/30/2014 09:00</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0003          |07/30/2014 09:40 | 07/30/2014 09:42</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0003          |07/30/2014 09:42 | 07/30/2014 09:55</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">0003          |07/30/2014 09:55 | 07/30/2014 09:59</span></div>
    <div> </div>
    <div><span style="font-family:'courier new', courier, monospace;">In origin there are two time tables (start, end). I want to create a new table with time difference of each line. In my output i need a summary of the time differences for each order + the differnce between the last end time of the first order and the first start time of the next order.</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">Maybe there is a solution with a loop?</span></div>
    <div> </div>
    <div><span style="font-family:'courier new', courier, monospace;">Thank you very much for your help.</span></div>
    <div> </div>
    <div><span style="font-family:'courier new', courier, monospace;">Best regards,</span></div>
    <div><span style="font-family:'courier new', courier, monospace;">Martin</span></div>
  • Matthew L.
    edited May 13, 2016 #4
    <p>Hello Martin,</p>
    <p> </p>
    <p>I've attached an example that replicates what you are looking for.</p>
    <p>I used the Header and Footer grouping rows to create the needed calculations.</p>
    <p>I also used a global variable to capture the value I needed for a carry-over.</p>
    <p> </p>
    <p>Please look over the example and let me know if you have any questions about it.</p>
    <p> </p>
    Warning No formatter is installed for the format ipb