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.

11 replies

null
    • Fred
    • 3 days 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
    • 3 days 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
      • 3 days 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
      • 3 days 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 days ago
      • Reported - view

       Thank you for the help~

    • Fred
    • 3 days 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 days 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 days 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 days ago
      • Reported - view

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

      • gold_cat
      • 19 hrs 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
    • 8 hrs 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
Login to reply

Content aside

  • Status Answered
  • 8 hrs agoLast active
  • 11Replies
  • 81Views
  • 2 Following