0

Finding the sum of the last entry given specific text in a field

Sorry for the nonsensical title.

I have a table called Bulk Inventory:

In a formula in a different table, I want to find the sum of the 'Cumulative (Laa)' values for the final entry of a given month for all tanks. So from the screenshot above, I want to add 10.14 plus 4.00 and so on.

I want the formula to work even if I add more tanks in the future.

By the way, loving Ninox so far! Once I have my database set up it's going to save me almost an hour per day on average!

4 replies

null
    • Lars
    • 2 yrs ago
    • Reported - view

    Hi Alex,

    just summarise all the separate values for one month, i.e. 0.00, 10.14, 40.00, 30.00 ... that will yield the same result and you can easily access these values with a select command on the Tank table and an appropriate month filter. It will go over all the Tanks in the table, no matter how many you'll add or remove

    It will be something like 

    sum((select Tank where month(date) = 2).fieldThatYouCumulate)

    Does this help?

      • Alex_Mumm
      • 2 yrs ago
      • Reported - view

      Lars Unfortunately that doesn't work because there are multiple records for each tank field in this Bulk Inventory table.

      Here's what I have so far: 

      let a := this;
let T1 := last(select 'Bulk Inventory' where Tank = "Beatrice" and month(Date) = a.Month).'Cumulative (Laa)';
let T2 := last(select 'Bulk Inventory' where Tank = "Dorothy" and month(Date) = a.Month).'Cumulative (Laa)';
let T3 := last(select 'Bulk Inventory' where Tank = "Experiments" and month(Date) = a.Month).'Cumulative (Laa)';
T1 + T2 + T3

      So you can see that currently I need a line of code for each tank (Beatrice, Dorothy, Experiments, etc) and then I add all the values afterwards. Is there a better way to do this?

    • Alex_Mumm
    • 2 yrs ago
    • Reported - view

    I think I didn't state my problem clearly enough, but I've found a solution: 

    let a := this;
let b := unique((select 'Bulk Inventory' where month(Date) = a.Month).Tank);
let total := 0;
let i := 0;
while i < count(b) do
    total := total + last(select 'Bulk Inventory' where Tank = item(b, i)).'Cumulative (Laa)';
    i := i + 1
end
;
total
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Alex

    Loops like this can be shortened to 

    let a := this;
let total := 0;
for b in unique((select 'Bulk Inventory' where month(Date) = a.Month).Tank) do
    total := total + last(select 'Bulk Inventory' where Tank = b).'Cumulative (Laa)';
end;
total

    Regards John

Login to reply

Content aside

Categories

View all topics
  • Status Answered
  • 2 yrs agoLast active
  • 4Replies
  • 90Views
  • 3 Following