I have 2 controls, Start Date and End Date. I would like to have the min and max of a particular field to be selected as default values of the controls. Is there anyway to do it. I tried creating a calculated field, max or min({field},,pre_filter) but later realized that we can’t add calculated field into a parameter. Any help/idea is much appreciated.
You can set up dynamic defaults for the parameters.
Here’s another post on dynamic defaults.
Try maxover or minover.
Did you considered using relative date filter which you may define default date range for Last N years? Understand that it is not exactly what you are asking but I think it is the most similar native feature
And there is a workaround. You need to create another dataset with 3 columns. 1st column is user name or user group. 2nd column is the min date. 3rd column is max date. May I know what is the data source, it will be easier to get the min date or max date if the data source is a RDBMS because you can use custom SQL to select min(date), max(date)
Then you can use the minDate and maxDate as dynamic default parameter. You may use the document shared by Max in above. Finally, you can use the parameters as the default Start Date and End Date
Meanwhile, we will put this as feature request
Hi @suraj.nimse Did either of these solutions work for you? Please let us know, and help the community by marking one of the answers as a “Solution.”
Hi Roy,
I already tried mentioned solution but its won’t work.
We are using Amazon Redshift.
Suraj Nimse
Associate Product Manager | Sokrati
Website: www.sokrati.com
can share some screenshot or error message why it is not working?
- You can create a dataset that looks like this:
In your dataset use SQL to calculate the MinDate and MaxDate. Depending on your use case, it can be the same value for all users or a different value for each user.
-
Add the above dataset to your analysis.
-
Create a datetime parameter for the start of your date range, e.g. FromDate. Click “Set a dynamic default” and select the dataset that you created, and the MinDate column.
-
Repeat step 3 for the end of your date range.
-
Create a date filter and select “Use parameters”. Select the parameters that you created as the start date and end date parameters.
Created this data set
Then Set Dynamic Default
Clicked on Apply
Now it is asking for selecting Default Date i.e Fixed Date or Relative Date
And without selecting it not going ahead.
If I select fixed date 2023/02/01 then by default this date appears in drop down control.
And min date & max date in dataset is 2022/11/02 and 2023/01/02.
I think the default date is used only if the reader’s username is not found in your dataset.
Is your own username in the dataset? You can test it to confirm.
- View the report using a user whose username is not in your dataset. I believe you should see the default date here.
- View the reporting using a user whose username is in the dataset. You should see the dynamic default in this case.