I’m using the Joined List to chart (with quickchart.io) values from a column where are stored data from users (last 60 days).
I would like to offer the view of the last 7, 30 and 60 days. Is any simple method to limit in some way the rows kept by Joined list (that gather all the values/rows that are in the column)? Any Suggestion?
Hey, have you tried using first an If Then Else column and after a join list?
1 Like
I think what @Lucas_Pires suggest is great. Create a column to calculate the difference in days between your record and now.
Then use 3 ITE columns: for last 7 days, if difference less than or equal 7 then data, else null. Do the same for the other 2 ranges.
Finally have 3 joined list columns linked to those 3 ITEs.
1 Like
Yes, this is simple and great! Thanks!!
2 Likes
I did something similar that worked well.
Date Sheet:
Column 1: Type (Today, 7 days, 30 days)
Column 2: Date (Today, Today - 7, Today - 30)
Performance Sheet:
3 Single Value Columns for Today, 7 Days and 30 Days
1 ITE “Date”: If Date Viewed is on or after: Today + If Date Viewed is on or after * 7 Days, Last 7 Days + If Date Viewed is on or after * 30 days, Last 30 Days.
Picker: User Specific Text Column (This is where your choice goes)
1 Single Value: “Performance Picker”
4 ITE:
- IFToday: If “Date” is Today, Today
- IF7Days: If “Date” is Last 7 Days, Last 7 Days + If Date is Today, Last 7 Days
- IF30Days: if “Date” is Last 30 Days, Last 30 Days + If “Date” is Last 7 Days, Last 30 Days + If “Date” is Today, Last 30 Days
- If “Performance Picker” is “IFToday”, True + If “Performance Picker” is IF7Days, True + If “Performance Picker” is IF30Days, True + If “Performance Picker” is empty, True
My Chart then uses a Filter: IFTrue is True
The reason I set it up this way is so that I can see all posts viewed Today, Last 7 Days and the Last 30 Days…but the Last 7 Days also includes posts seen Today and the Last 30 days includes all posts seen Today + Last 7 Days as well.
Previously when I viewed Last 7 days, it excluded all viewed Today. And when I viewed Last 30 days, it excluded all viewed Today and Last 7 Days.
2 Likes
timtoady!!
I solved this challenge using a slightly different approach.
I also have Today (Past 24 hours), Past 7 Days and Past 30 Days choices, but instead of writing the choice labels into the User Specific column, I write a value - representing the number of minutes.
So…
- Past 24 Hours = 1440
- Past 7 Days = 10800
- Past 30 Days = 43200
Then in the table that contains all the rows I want filtered, I calculate the age of each record using a bit of date math:
And so the if-then-else to determine which rows to filter simply compares the two numbers. If the row age (in minutes) is less than the user selection, it gets included.
3 Likes
…and is it possible to filter a joined list with values in the column related only to a specific user?
You would have to do a relation as a source of the joined list. The relation would use the link as the user’s email/ID or something like that.
1 Like
yes, i’m trying with a relation to the email, but it doesn’t work. Maybe I’m missing something.
Can you go into more details about what you’re trying to do?
Every day users fill up a row with some data. So I have multiple rows with different users each day. I would like to chart the data (one column + date) for each user. Joined list + Quickchart is perfect, but Joined list store all the data for each row (therefore mixing different users).
Like @ThinhDinh mentioned, assuming you have an email with that data, you should be able to create a relation to it using email and build your joined list from that relation.
2 Likes