I have parameter for number value that is Year, shown as a drop down not a date control , and another text parameter that also shows a drop down of values.
I want for both parameters to show as defualt the max value, for the string drop down , my values are strcutures like (ID - Description), so the max on the ID will give me latest value needed.
These are just basic defaults needed for all users.
Is there a way to do this basic operation or do i still need to create a new seperate dataset with all users list and columne per field and all that?
If so , is there an easy way to do all this direclty in QS to run the list user command in the documentations and then directly append to it the two columens with just the one calculated value ? or will i need to store the list of users somewhere and then join it with a second dataset that does the calculation of the single values ? & is there road map to adda simple calculated default value ?
Hello @ame54, I think I may need a little more information about what you are expecting to accomplish, but I’ll try to explain and we can go from there.
First off, you cannot apply something like a max aggregation to a list of dropdown values. From what I can tell, your max value that you are looking for is related to the max date related to that specific ID, so there may be a work-around for something like that. Either in custom SQL or a calculated field on the dataset, you could build you ID - Description field but also append the date or year value in front of ID so it will order the list by most recent. It won’t look amazing, but it would ensure you are seeing it closer to the order you are expecting.
As for default values on parameters, if the default can match for all users, you can set a static default value. Otherwise, if it is dependent upon the user, you can utilize a dataset that contains a column with the UserNames or GroupNames, and a 2nd column matching your dropdown to determine the value that they should be defaulted to. Then this will be applied to your analysis and used to build a dynamic default value for the parameter. I’ll link some documentation on parameter default values below:
So the need is for a Dynamic default value for all users.
So for example dahsboard will show catalog of items, and we have various catalogs.
The catalog version value is a column, and i want my paramter to have default value of Max(version) of the catalog so when a new catalog is loaded or created in, users dont need to set it themselves.
Another simple example, is i have year column and i want latest year, to be default value for a parameter.
Hello @ame54, alternatively, you could avoid directly filtering by the parameter you have set, and instead filter by using a calculated field related to that parameter. This will require you to have single value parameters, as multi value parameters cannot be used in calculated fields. Basically, I would set both dropdowns to not have a default value. This will ensure they display select all. Now, you can use your calculated field to check if no default is set, then display the values you are wanting, otherwise display the parameter value filter. Now, if you want the user to be able to use Select All, then you will need to use a nonexistent value as your default, you could literally add Default or 0000 for your Year field, and use that to filter. Anyway, this is what the calculation would look like:
Include isNull if you want to remove the default value, or just check for the nonsense default value.
ifelse((isNull(${parameter}) OR ${parameter} = 0000) AND extract('YYYY', now()) = {Year}, {Year},
isNotNull(${parameter}) AND ${parameter} = {Year}, {Year},
NULL)
Here, this will return the most recent year on load, or return the year selected in your parameter. You will use this calculated field in your field well instead. That is likely the best work-around to this issue if you don’t want to use dynamic default values. Let me know if you have any further questions!
hmm interesting process, but does sounds complicated and no doesnt really resolve issue as i need multi select drops downs, as its not a one value or all type of situation, and also introduction of dummy values will just create confusion for users.
Regarding the suggested creation of a dataset listing all possible users and then duplicating values for default values solution in the documentation, i did not fully understand how to create this in the documentation , is it to run CLI command to get list of users and then importat that in S3 or redshift and then append that data with the default values ?, does that mean i need certain admin rights to be able to run these commands to create this dataset? sorry but did not fully understand process needed.
Hello @ame54, I did realize there is an alternative for multi select dropdowns where you can utilize the in aggregation to check for a value that way. That would allow you to maintain the parameters you have set as they are. I’ll link the documentation:
Also, in to avoid using a non-existent value as your default, you should be able to use All or NULL as the static default and it should set the value to select all on load if it is a list of strings. That would avoid using a value that doesn’t exist and not confuse the user.
I think this option would likely be the best case scenario to avoid the alternative. You would need to be able to run the ListUsers command either in the CLI or boto3 SDK and then add that user list to S3 where you could merge the list of users with the anticipated default value in a SQL query. I think the initial option can work if we test it out a bit.
Hello @ame54, did my previous response help guide you towards your expected output? I will mark it as the solution, but please let me know if you have any further questions on this issue and I can guide you further. Thank you!