Hi Team,
We are trying to create a control that displays last n days dynamically based on the current date. Dataset gets refreshed daily so we would like that control to get updated dynamically.
Can someone please help us on this?
Thanks!!!
1 Like
Hello @Madhan482, technically there is an option to manage something like this utilizing the relative default date option if you utilize a parameter based control. You can set up the options to default the initial filter date to Last N Days, I’ll post a screenshot of that below:
That is the best way to manage something like this. Let me know if that helps!
Hello @Madhan482, since we have not heard back from you with any follow-up questions on this issue, I will mark my last response as the solution. Please let me know if you still need assistance, and I can guide you further. Thank you!
Hi @DylanM
Sorry for the late response. Thanks for your suggestion.
Actually we tried this already but we had no luck. So, the problem is we have date field populated in the format 20240522(YYYYMMDD). How can we achieve the same in this case?
Also We have another scenario where we would like to display results based on months and the format for this field is 202405(YYYYMM).
Could you please help us in creating 2 controls as below:
- Control to view last 5 days. Ex. 20240522,20240521,20240520,20240519,20240518
- Control to view last 3 months. Ex. 202405,202404,202403
Please note that all these should be dynamic and should roll based on current date. Thanks in advance.
Hello @Madhan482, due to the format of your date field as a string value, it will be a little more complicated and the options for user interactions on filtering will be limited but it is possible. For example, if you wanted the default to show the last 2 weeks of data, you can use functions like extract to determine how the date today compares to your date field. You will experience some complications when it comes to handling dates across months and years but I will try and assist.
I’ll write an example below:
ifelse(parseInt(substring({date}, 5, 2)) = extract('MM', now()) AND parseInt(substring({date}, 7, 2)) <= extract('DD', now()) AND parseInt(substring({date}, 7, 2)) >= extract('DD', addDateTime(-14, 'DD', now())), {date}, NULL)
The above calculation would account for dates within the last 14 days to return any values that occur within that range. In order to make this work with user selections though, you will need to check for a lot of different variables. I think the best route for you would be to use a mix of concat and substring functions, along with parseDate, to convert your unformatted string date field to a datetime datatype field and build from there.