0

How to get a filtered sum() from a table-value, where all have a common category?

I need to get the sum() Formula working that it not only gets me the sum of a specific field of a table, but also a filtered value. Like, all data-entries have a numbervalue and i just want to have the sum of all which have one specific value from a selection-field.

I can´t get it working.

And i hope its easy to do, because it shouldnt be that hard.

8 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Can you post the code that you are working with?

      • Michael_Blechinger.1
      • 2 yrs ago
      • Reported - view

      Fred i was trying to implement this, which is from the website: 

      sum((select Invoices.Total where year(date) = 2021).Total)

      https://docs.ninox.com/en/script/functions/sum?q=sum

       

      Then i tried to make it working like this, but there is some mistake: 

      sum((select Studypoints.Studypoints where Studypoints.StatsWoche.Studypoints.Abteilung = 1).Studypoints)
    • Fred
    • 2 yrs ago
    • Reported - view

    1) after the select you have Studypoints.Studypoints. After a select statement you only need the table name. You do not need any field names, so the 2nd Studypoints is not needed.

    2) after the where I'm confused by the path. You are starting in the Studypoints table (which is what the select tells me) so you don't need to repeat it. Since Ninox allows you to add the StatsWoche link then that tells me it is a reference field. But why are you going back to the Studypoints table and then ending with the Abteilung field name from the Studypoints table?

    Could something like this work for you? 

    sum((select Studypoints where Abteilung = 1).Studypoints)
    • Michael_Blechinger.1
    • 2 yrs ago
    • Reported - view

    Thanks, this is already better than before! Now i have just the issue, that i dont have the sum of the numbers, which are just for a specific week, which is in this 1:N table because i connected all entries for studypoints to this entry, which sums all numbers for the week.

    i hope you can understand what i need.

    To your question, i dont understand so much about it, what you ask, and i dont know about the Ninox Language, because i cant find some good reference-guide, to learn it from the ground up.

    So i need from your formular which outputs the sum of all entries with the Abteilung = 1, that i just takes this ones, which are in this week. so which are in the 1:N table.

     

    This works for get the sum of all points of the week: 

    sum(Studypoints_bei_Stats.Studypoints)

    And this for the sum of all ever added points with the department = 1 

    sum((select Studypoints where Abteilung = 1).Studypoints)

    I need now the combination of both :-)

    • Fred
    • 2 yrs ago
    • Reported - view

    You can try something like: 

    sum(Studypoints_bei_Stats[Studypoint.Abteilung = 1].Studypoints)

    This is assuming that the 1:N relationship is between Studypoints and Studypoint_bei_Stats. If not then you have to figure out how to get from Studypoints_bei_Stats to Studypoints.

    To filter a relationship you have to use the square brackets. The nice thing about them as well is that you can use them in select statements in place of where, so now you can only use the square brackets to do all your filtering.

    So your select statement looks like this: 

    sum((select Studypoints[Abteilung = 1]).Studypoints)
    • Michael_Blechinger.1
    • 2 yrs ago
    • Reported - view

    Somehow its almost working. I still dont get the numbers from the specific week.
    I also think i was writing it ab bit confusing.

    The structure is like this:

    Stats: contains 1:N connected to Studypoints, which has the Tablename "Studypoints_bei_Stats"

    Stats contains also an Numberfield where i write the week in numbers, like "2022,30"

    Stats also contains a formula-field where i want to get the sum of the Studypoints in the 1:N Connection which are related to just one of the Divisions (Abteilung)

    i guess i did a small mistake in this formula: 

    sum((select Stats).Studypoints_bei_Stats[Abteilung = 3;
        'BD-GAME-WEEK' = Geburtstagsspielwoche].Studypoints)
    • Fred
    • 2 yrs ago
    • Reported - view

    If I understand your structure:

    You have two tables: Stats and Studypoints.

    They are linked by the reference field Studypoints_bei_Stats where 1 record in Stats is linked to many records in Studypoints.

    If that is true then the following should work: 

    sum(Studypoints_bei_Stats[Abteilung = 1].Studypoints)
    • Michael_Blechinger.1
    • 2 yrs ago
    • Reported - view

    Excellent, it works!! Thanks a lot! :-)

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 8Replies
  • 531Views
  • 2 Following