Hi @camsd047
If i’ve understood your requirements right, then there are a few ways to achieve your requirements, let’s break them down and see if they help you achieve what you’re looking for. I’ve posted different ways of achieving your requirements, as it’ll be based on what you’re looking for and how you want to use your data. In all the information I provide below, you can tailor the components how you need them.
- A hidden ‘timestamp’ field is included in the form, configured to ‘current date/time’, to collect the date/time it was submitted. When reporting, I want to be able to generate graphs based on the last week/month/6months/year etc, but I can’t work out how to generate a subset on the timestamp field that would achieve this? Is it possible?
Yes, it’s possible 
I’m assuming you want the graph to show the last X months/years from the current date?
If so, you will need to build a few Functional Composites that will be used within the subset filters.
Functional composite 1: Current date/time.
Functional composite 2: Add/Subtract interval.
In functional composite 2, you would specify Functional Composite 1 (current date/time) as the field value, and then specify the desired interval, configuring the days/months/years from the current date/time you would like (e.g. 30 days)
Then you can create a subset to identify a whether the timestamp fits within a the range. You would specify your subset filter as such:
To calculate record from X time in the past, to now:
Functional Composite 2 > Greater than or Equal to > Functional Composite 1
To calculate record from X time in the future, to now:
Functional Composite 2 > Less than or Equal to > Functional Composite 1
This would effectively place the record in the subset if the timestamp is greater/less than, 30 days in the past/future.
- Similar to above, I need to break the data down by hour of the day (09:00-10:00, 10:00-11:00 etc), so busy periods can be identified and staffing arranged as required. Again, I can’t work out how to create the correct conditions in the subsets menu to allow this.
It’s difficult to advise without seeing how you want to use this subset but i’ll give you some generic help here. Depending on what you’re looking for there’s two different way of doing this, again depending on how you want to utilise the data:
Solution 1: Based on field value
You would need to create a subset for each hour range through out the day.
If you’re using a timestamp, then you would firstly need a Functional Composite to extract the time from the timestamp.
Functional Composite 1: Field’s date/time format
In this composite you would select the output of ‘Time’. This will extract the time from the timestamp.
Subset 1:
Functional Composite 1 > Greater than/Greater than or Equal to > Start of hour range (e.g. 09:00)
AND
Functional Composite 1 > Less than/ Less than or Equal to > End of hour range (e.g. 10:00)
You would repeat this subset and change the start/end ranges for each hour of the day you want to capture.
The record will be in the subset based on the whether the extracted time is within the time range (e.g. 09:00 - 10:00)
Solution 2: Based on current time
Not ideal and very long winded, but you would need to create a subset for each hour range through out the current day/time. Then we need to compare the time field to each subset (i.e. susbet-within-a-subset)
If you’re using a timestamp, then you would firstly need a Functional Composite to extract the time from the timestamp.
Functional Composite 1: Field’s date/time format
In this composite you would select the output of ‘Time’. This will extract the time from the timestamp.
Functional Composite 2: Current date/time format
In this composite you would select the output of ‘Time’. This will extract the time from the current date/time.
Now we need to use a subset within a subset.
Set of subsets 1: Identifying current date/time ranges
Functional Composite 2 > Greater than/Greater than or Equal to > Start of hour range (e.g. 09:00)
AND
Functional Composite 2 > Less than/ Less than or Equal to > End of hour range (e.g. 10:00)
You would repeat this subset and change the start/end ranges for each hour of the day you want to capture.
Subset 2:
Functional Composite 1 > Greater than/Greater than or Equal to > Set of subset 1 (start of hour range)
AND
Functional Composite 1 > Less than/ Less than or Equal to > Set of subset 1 (end of hour range)
You would repeat this subset and change the start/end ranges for each subset.
The record will be in the subset based on the whether the extracted time is within the time range (e.g. 09:00 - 10:00)
Solution 3: Within chart configuration
Based on requirement 1, i’m not sure if this is for usage within a chart, so adding this in just in case you are.
You would only need to extract the time from your timestamp in order to get a chart to display each hour. With this method, there is no need to extract any data from dates/timestamps.
Within your chart, set the ‘Group field’ to your field (e.g. timestamp). A new field will appear called ‘Scale’ which will allow you to group the date based on a scale. You would simply choose ‘Hour’ or ‘Hour of day’ to group your chart’s data by hour.
Solution 4: Searching and filtering
You can create a search form which will allow you to search and filter your data based on a time range.
Within your search form you could add in your timestamp value and set the default value to be ‘Relative’ and specify your value there.
If you didn’t want to do that, you can add your timestamp field into the search from twice, set to the presenter ‘Input time’
The first would have a comparator of ‘Greater than’ or ‘Greater than or Equal to’ and would be the start range.
The second would have a comparator of ‘Less than’ or ‘Less than or Equal to’ and would be the end range.
This would provide you with a method of being able to search and filter data between the time range specified.
Hope this helps and isn’t too confusing
If I haven’t covered your use-case, please let me know and provide as much detail of how you want to use the data on your page, and I will try and provide more contextual build advise.