0

How to write code for data filtering?

How can we filter out the top three salespeople with code?

Xiao Ru    $382.00
Jiang Ningwei    $648.00
Xiao Ru    $1,704.00
Jiang Ningwei    $2,883.00
Xiao Li    $11,760.00
Yang Daibing    $4,640.00
Li Yanping    $980.00

How can we filter out dates so that only '2024/2/29' and '2024/3/1' are displayed with code?

Feb 29, 2024, 22:04
Feb 29, 2024, 22:14
Mar 1, 2024, 08:11
Mar 1, 2024, 08:13
Mar 1, 2024, 08:17
Mar 1, 2024, 08:21

Thanks for your help! Thanks in advance.

18 replies

null
    • Fred
    • 2 mths ago
    • Reported - view

    Look at this reply to see an example of order by. Look at the full post to see an example of slice().

    If you still need help, please post your code?

    • Fred
    • 2 mths ago
    • Reported - view

    The second one is a filtering question, so you could do something like:

    [datefield = date(2024, 2, 9) or datefield = date(2024, 3, 1)]
      • gold_cat
      • 2 mths ago
      • Reported - view

       
      Thanks for Fred's reply. I may not have expressed my question clearly before.

      • For the first question, I have calculated the total sales volume of each employee. But I need to use code to filter out the top three with the most sales volume. How can this be achieved?
      let getCopy := (select Data);
let Onlyname := unique(getCopy.name);
let newData := for loop1 in Onlyname do
        let TotalSales := sum(getCopy[name = loop1].MN);
        {
            name: loop1,
            TotalSales: TotalSales
        }
    end;
      • For the second question, I have set date + time in the database. They span September and October. I only need to use code to filter and display as 2024/9 and 2024/10. How can this be achieved? (If the time span is from January to May, then the filtered results are 2024/1, 2024/2, 2024/3, 2024/4, 2024/5, and so on.)   

        For the code of this problem, I don't know where to start.

      test.ninox
      • Fred
      • 2 mths ago
      • Reported - view

      1) try:

      let getCopy := (select Data);
let Onlyname := unique(getCopy.name);
let newData := (for loop1 in Onlyname do
        let TotalSales := sum(getCopy[name = loop1].MN);
        {
            name: loop1,
            TotalSales: TotalSales
        }
    end order by -number(TotalSales));
slice(newData,0,3)

      Line 9, adds the order by function, converts TotalSales into a number then does a descending sort with the negative sign.

      Line 10, uses the slice() command to find the first three iterations.

      • gold_cat
      • 2 mths ago
      • Reported - view

       Thank you for the help~

    • Fred
    • 2 mths ago
    • Reported - view
     said:
    I only need to use code to filter and display as 2024/9 and 2024/10. How can this be achieved?

    Look into the yearmonth() command. Create a new formula field with the command.

    Now you can do filters based on the data. The results is a string "2024/09" or "2021/12". Look for the year/month combinations you want.

      • gold_cat
      • 2 mths ago
      • Reported - view

       This part, the `yearmonth()` function does not support time group conversion. How can I use the `yearmonth()` command? In actual situations, I also need to include the day in the date, that is, for example, 2024/9/13. How can I achieve this? (I thought of a clumsy method below. Add an equation after each data to calculate the result, but I want to implement it with code.)

    • Fred
    • 2 mths ago
    • Reported - view
     said:
    I also need to include the day in the date, that is, for example, 2024/9/13

    It is hard to keep up with the changing requirements. 😉

    If you need the date from a date + time field then use the date() command.

    I would recommend that you create the following fields as well:

    1. month()
    2. year()
    3. quarter()

    Now you can sort/group by any of these fields and create nice tables and charts.

    If you need to format date into other ways then you should be aware of this page. Make sure to check out the link at the bottom for date and time formats.

      • gold_cat
      • 2 mths ago
      • Reported - view

       Yes, my ideas are greater than my practical ability. But thank you for your help.

      • gold_cat
      • 2 mths ago
      • Reported - view

      Hello, I didn't clearly express my thoughts yesterday.
      What I wanted to say is: apart from the method I mentioned of creating a conversion function in the referenced time table, is there another way to achieve the functionality I mentioned by only editing the code? If so, is it complex? Because with my current knowledge, I am unable to handle this problem, so this is just for discussion.

    • Fred
    • 2 mths ago
    • Reported - view
     said:
    is there another way to achieve the functionality I mentioned by only editing the code?

    Using the DB you attached, if you wanted to search for only two dates in the Data table you could do something like, by the way renaming the date + time field to just time is confusing:

    select Data where date(time) = date(2024,9,4) or date(time) = date(2024,10,1)

    Where if you added fields to the Data table that broke down the date+time field into usuable data it will make things like this easier.

    If you had a date field then it would change to:

    select Data where date = date(2024,9,4) or date = date(2024,10,1)

    Or if you had a year field:

    select Data where year = 2023 or year = 2022

    Or if had a quarter field:

    select Data where quarter = 2 and (year = 2022 or year = 2023) order by year
    • gold_cat
    • 2 mths ago
    • Reported - view
     said:
    renaming the date + time field to just time is confusing

    That's the problem. If I simply add date(), there's no constraint, and all times will appear.

    • Fred
    • 2 mths ago
    • Reported - view
     said:
    test.2 is the second method that was discussed (it did not correctly output the total for the specified date).

     You wrote:

    let getCopy := (select Data);
let Onlyname := unique(format(date(getCopy.time), "yyyy/MM/dd"));
let newData := for loop1 in Onlyname do
        let TotalSales := sum(getCopy[format(date(time), "yyyy/MM/dd") = loop1].MN);
        {
            name: loop1,
            TotalSales: TotalSales
        }
    end;
newData

    Line 2, if you check out the date and time format, you will see that dd gives you the day of the week.

    To make things easier, try to leave formatting till the very end so that Ninox can have the data in the proper form to use in filters/searches.

    Try:

    let getCopy := (select Data);
let Onlyname := unique(getCopy.date(time));
let newData := for loop1 in Onlyname do
        let TotalSales := sum(getCopy[date(time) = loop1].MN);
        {
            name: format(loop1, "yyyy/MM/DD"),
            TotalSales: TotalSales
        }
    end;
newData

    Overall, I removed the format() from the first few lines and only put in the end (line 6).

    Line 2, I moved the getCopy in front of date(). I can't explain the difference but if you create a new formula field and put your way you get 1 results and if you switch it to my way you get the results you want.

    Line 6, notice I changed dd to DD.

    and you will get something like:

      • gold_cat
      • 2 mths ago
      • Reported - view

       Great. Great. Great. Fred always finds the most efficient solutions. 

Login to reply

Content aside

  • Status Answered
  • 2 mths agoLast active
  • 18Replies
  • 133Views
  • 2 Following