0

Sum of fields for a period of time

I have a table "credit_Fill" and the second table "Tables". In the Credit table there is a credit field, in which credits are recorded that are related to a particular table. I need to sum up the amount of these Credits for a period of time, from the last check to now. The time span is determined by the time difference between the two fields "Date + Time", which are in different tables "Hourly Estimation" and  "Credit_fill"

let xBeg := last('Hourly Estimation'.'Date + Time');
let xEnd := Credit_fill.'Date + Time';
let t := this;
sum(first((select Credit_fill)[Date >= xBeg and Date <= xEnd]
[Date = t.Date and 'Table Name' = t.'Table Name'].CREDIT))
 

What's wrong with this formula?

17 replies

null
    • Fred
    • 1 yr ago
    • Reported - view
    iliper LTD said:
    sum(first((select Credit_fill)[Date >= xBeg and Date <= xEnd] [Date = t.Date and 'Table Name' = t.'Table Name'].CREDIT))

     what is second set of [ ] for? you see you have Date used three times. Maybe try:

    sum(first((select Credit_fill)['Table Name' = t.'Table Name' and Date >= xBeg and Date <= xEnd].CREDIT))
    • iliper LTD
    • iliper_LTD
    • 1 yr ago
    • Reported - view
    Fred said:
    what is second set of [ ] for?

    Find table "Credit_fill" and calculate the time interval where the table formation date and the table accounting date and the table name are the same, the last report start date and the end date are now. 

    We check tables as needed, and if there is credit on the table, we must take it into account. The amount may change. We have created a formula, but our problem is that when Credit is seen for so much and our field takes this amount, he writes it in all fields, before and after the credit, throughout the day. It is important for us that if a credit is seen for a table at 5 p.m., it should not be displayed in reports that were made earlier

    • iliper LTD
    • iliper_LTD
    • 1 yr ago
    • Reported - view
    Fred said:
    sum(first((select Credit_fill)['Table Name' = t.'Table Name' and Date >= xBeg and Date <= xEnd].CREDIT))

    Doesn't work, it shows result - Zero

    • Fred
    • 1 yr ago
    • Reported - view

    I just realized you have a first in there. Which doesn't make sense since you want to sum a collection of records.

    sum(first((select Credit_fill)[Date >= xBeg and Date <= xEnd]
[Date = t.Date and 'Table Name' = t.'Table Name'].CREDIT))

    In addition I just noticed that:

    let xEnd := Credit_fill.'Date + Time';

    points to Credit_fill as a reference field. Then you do a select later. Why not just do:

    Credit_fill[Date >= xBeg and Date <= xEnd]

    Assuming that 'Table Name' is the name of the table you are on.

    For me when I'm troubleshooting selection/filtering I first verify that it finds the correct records.

    Make another formula field and put this in for the formula:

    let xBeg := last('Hourly Estimation'.'Date + Time');
let xEnd := Credit_fill.'Date + Time';
let t := this;
let x := (select Credit_fill)['Table Name' = t.'Table Name' and Date >= xBeg and Date <= xEnd]
concat(x)

    Then verify that it has pulled the correct records. If not, then just simplify the filtering until it is correct then add back.

      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

      Fred I probably need to remove the end date, and just sum up all the values of the credit field. then I will get the amount I need. if I start checking this in the morning, from 10.00, then the скувше is not available, because it was not issued. And so I check every other hour. And as an example, at 12.00 a credit was seen on this table. And it enters the table with the mark 12.01. But the situation is that it is displayed with 10.00 and 11.00 in the report. And I can not make it so that it does not appear in the report before it is issued.

      This is my task, do not display credit in the table check until it is issued

    • Fred
    • 1 yr ago
    • Reported - view

    Is the field Date from the Credit_fill table a date field or a date/time field?

    • Fred
    • 1 yr ago
    • Reported - view

    Your xEnd needs a first() or last() function, like in the xBeg, as well, since you are making an array of Date + Time data from the Credit_fill link.

    let xBeg := last('Hourly Estimation'.'Date + Time');
let xEnd := Credit_fill.'Date + Time';
      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

      Fred Thanks, today at the webinar I’ll ask Maria, I don’t get what I need)
      thank you for always responding to my messages and helping

      • Fred
      • 1 yr ago
      • Reported - view

      iliper LTD You are welcome. I got an email yesterday that the Tuesday webinar has been cancelled.

      Can you post a sample of your db with some records that are inside and some that are outside the date/time range?

      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

      Fred Today was a webinar. I ack to Maria for help, she always helps and finds the right solution for me. Today she pointed out the path that I should develop. My error is in using the DATE field. Need to use Date+Time

      let t := this;
      first((select Credit_fill)['Date + Time' <= t.'Date + Time' and 'Table Name' = t.'Table Name'].CREDIT)

      This is also not a solution, but I think that we need to use this way

       

      Fred said:
      Can you post a sample of your db with some records that are inside and some that are outside the date/time range?

      Such a task:

      I Have two tables "TABLE1" -  fields,    "Date+Time1" now()   Text field "Name" and "Number1"

      And "TABLE2"- with  fields   "Date+Time2" now()   Text Fields "Name" and "Number2" - formula field  with formula

      let t := this;
      first((select Table2)['Date + Time' <= t.'Date + Time' and Name = t. Name ].'Number 1')

      At the beginning we create a record in the  "TABLE1" with the title in the text field 'aa' and same record in "TABLE2"

      After a couple of minutes we create record in "TABLE1" Name  'aa' and put any number in field Number . After we make an record  in "TABLE2" and in text field 'aa'. It is necessary that after creating the second record with record text field 'aa' in table2, it shows the number from the second record table1. But this figure should not be displayed in the first record in table2, because it was made later than the record come in the "TABLE2"

      TEST.ninox
    • Fred
    • 1 yr ago
    • Reported - view
    iliper LTD said:
    After a couple of minutes we create record in "TABLE1" Name  'aa' and put any number in field Number . After we make an record  in "TABLE2" and in text field 'aa'. It is necessary that after creating the second record with record text field 'aa' in table2, it shows the number from the second record table1. But this figure should not be displayed in the first record in table2, because it was made later than the record come in the "TABLE2"

    So I add the value 20 to the second record in Table1 and I get this in Table2:

    In fact this happen regardless of what is in the Name field since you do not take into account the Name field in your formula. If you want the Name field to match then you can try adding:

    let t := this;
first((select Table1)[Name = t.Name and 'Date + Time1' <= t.'Date + Time2'].'Number 1')
      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

      Fred your formula works very well, I fixed only one, added the 'last', without it the formula showed only the first information. Now the formula looks like this

      let t := this;
      first(last((select Table1)[Name = t.Name and 'Date + Time1' <= t.'Date + Time2'].'Number 1'))

      Thanks for your time and help.

      • Fred
      • 1 yr ago
      • Reported - view

      iliper LTD Just a minor change suggestion. Since you need the last record you can get rid of the first() function.

      let t := this;
last((select Table1)[Name = t.Name and
    'Date + Time1' <= t.'Date + Time2'].'Number 1')
      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

      Fred Perfect Thanks 

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 17Replies
  • 142Views
  • 2 Following