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
-
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?
-
The second one is a filtering question, so you could do something like:
[datefield = date(2024, 2, 9) or datefield = date(2024, 3, 1)]
-
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.
-
said:
I also need to include the day in the date, that is, for example, 2024/9/13It 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:
- month()
- year()
- 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.
-
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
-
said:
renaming the date + time field to just time is confusingThat's the problem. If I simply add date(), there's no constraint, and all times will appear.
-
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:
Content aside
- Status Answered
- 1 mth agoLast active
- 18Replies
- 133Views
-
2
Following