Working with dates in Configuration Blocks

[Pamir Rahimzadeh]

Hi Community,

I am working on an automation services job to open a dashboard, set a Date range filter within my data according to the current date, then send an email with the visualization. 

I am using the configuration block SetFilter statement where I would like to set the highValue parameter to a specific date, based on the current date (I am pulling weekly metrics, so I would like the highValue to be set to the most-recent Saturday based on the date of the automation services job being run).

I see examples online where one can insert a field, such as {date}, but my use case is more for a calculated value, where the report should be sent on Monday (for example), but the filter should be set to show data through the previous Saturday.

Would my solution have to utilize document properties with some sort of calculation? If so, how would I set up such a document property?

 

Any help would be appreciated!

[David Boot-Olazabal]

Hi Pamir,

I would think you would need a couple of calculated columns.
You need to calculated the day number for previous Saturday as well  as the day of sending. Both will result in a the day number.
This number can then be used to calculate the days between the day of sending and the previous Saturday, to filter out how many days you need to 'go back in time' to filter.

I don't have an example of the code, but maybe this gets you started.

Kind regards,

David

Edited October 16 by David Boot-Olazabal

[Pamir Rahimzadeh]

10 hours ago, David Boot-Olazabal said:

Hi Pamir,

I would think you would nee a couple of calculated columns.
You need to calculated the day number for previous Saturday as well  as the day of sending. Both will result in a the day number.
This number can then be used to calculate the days between the day of sending and the previous Saturday, to filter out how many days you need to 'go back in time' to filter.

I don't have an example of the code, but maybe this gets you started.

Kind regards,

David

Hey David, this all makes sense. How would you subtract from dates in a configuration block? Or how would you use a calculated column in a configuration block? I have seen examples where these are static sorts of values provided by the user creating the automation job. Is there a way to provide dynamic values, outside of something like {"date"}, which uses the current date? I guess I am asking, is there any guidance document on what possible dynamic values and operations can be performed on configuration block parameters? Specifically those with dates?

[David Boot-Olazabal]

Hi Pamir,

We don't think dynamic parameters is doable in a configuration block, since the syntax is 'parameter=value'.

It may be possible though, to come up with an alternative solution that involves sending the query to the database, based on the subtraction of the mentioned dates. You have make it work via the WHERE clause.

This stackoverflow post may give you an idea how you could use dynamic dates in the WHERE clause: https://stackoverflow.com/questions/50954008/need-dynamic-dates-in-my-sql-where-clause.

Kind regards,

David

[Pamir Rahimzadeh]

On 10/8/2024 at 2:15 AM, David Boot-Olazabal said:

Hi Pamir,

We don't think dynamic parameters is doable in a configuration block, since the syntax is 'parameter=value'.

It may be possible though, to come up with an alternative solution that involves sending the query to the database, based on the subtraction of the mentioned dates. You have make it work via the WHERE clause.

This stackoverflow post may give you an idea how you could use dynamic dates in the WHERE clause: https://stackoverflow.com/questions/50954008/need-dynamic-dates-in-my-sql-where-clause.

Kind regards,

David

Hi David,

Thanks for checking in. I think I pretty much did that with document properties, where I create them as follows:

Property 1, startDate: [Date Col] > dateadd('yy', -2, Date(Year(DateTimeNow()), Month(DateTimeNow()) - Month(DateTimeNow())+1, Day(DateTimeNow()) - Day(DateTimeNow())+1)) #this navigates to the first day of the first month, 2 years ago

Property 2, endDate: [Date Col] < dateadd('mm', -6, DateTimeNow()) #this navigates back 6 months, just using it as a placeholder until I can sort out how to use the document properties in configuration blocks.

 

My issue now is, I am unable to get those document properties to work within the configuration block. I tried searching through Spotfire documentation but could not find an example of document properties being used in Automation Services configuration blocks. Automation services does allow you to insert a generic document property field into a configuration block, as follows "{documentProperty:<propertyName>}". I have tried to replace <propertyName> with the following without success:

SetFilter(columnName = "Date Col", lowValue = {documentProperty:"startDate"}, highValue = {documentProperty:"endDate"}); 

SetFilter(columnName = "Date Col", lowValue = {documentProperty:<startDate>}, highValue = {documentProperty:<endDate>});

SetFilter(columnName = "Date Col", lowValue = {documentProperty:<"startDate">}, highValue = {documentProperty:<"endDate">});

 

Any thoughts on what I am doing wrong? I know my document properties are working, because I can "Limit Data Using Expression" on my visualization using the document properties and the visualization filters as I would expect.

 

I get the following error:

 

Executing task 1 of 3: Open Analysis from Library '_path'
Error executing task 1 of 3: Open Analysis from Library '_path'
System.InvalidOperationException: Cannot expand document property tag, since property '"startDate"' was not found in '_path. ---> System.Collections.Generic.KeyNotFoundException: The property named '"startDate"' could not be found.
   at Spotfire.Dxp.Data.DataPropertyRegistry.GetProperty(DataPropertyClass propertyClass, String propertyName)
   at Spotfire.Dxp.Automation.Framework.TagReplacement.GetDocumentProperty(AnalysisApplication application, String propertyName, Boolean isEditMode)
   --- End of inner exception stack trace ---
   at Spotfire.Dxp.Automation.Framework.TagReplacement.GetDocumentProperty(AnalysisApplication application, String propertyName, Boolean isEditMode)
   at Spotfire.Dxp.Automation.Framework.TagReplacement.Tag.TryReplaceCompoundTag(AnalysisApplication application, String& textToExpandIn)
   at Spotfire.Dxp.Automation.Framework.TagReplacement.Tag.ExpandIfFound(AnalysisApplication application, String textToExpandIn)
   at Spotfire.Dxp.Automation.Framework.TagReplacement.ExpandTags(AnalysisApplication application, String containingTags)
   at Spotfire.Dxp.Automation.Tasks.Tasks.OpenAnalysisFromLibrary.ExecuteCore(TaskExecutionContext context)
   at Spotfire.Dxp.Automation.Extension.Task.Execute(TaskExecutionContext context)
Done

Edited October 9 by Pamir Rahimzadeh

[David Boot-Olazabal]

Hi Pamir,

I know from Iron Python code, that you have to declare a document property a bit differently. Could you try this line:
 

SetFilter(columnName = "Date Col", lowValue = {documentProperty:["startDate"]}, highValue = {documentProperty:["endDate"]});

Kind regards,

David

[Pamir Rahimzadeh]

On 10/10/2024 at 8:16 AM, David Boot-Olazabal said:

Hi Pamir,

I know from Iron Python code, that you have to declare a document property a bit differently. Could you try this line:
 

SetFilter(columnName = "Date Col", lowValue = {documentProperty:["startDate"]}, highValue = {documentProperty:["endDate"]});

Kind regards,

David

Hi David, I get a similar error attempting this. I also attempted document property notation as one would in the .DXP (with ${} notation) and did not have any success.

 

I have used a workaround by utilizing the document properties to limit data in each visualizations, essentially bypassing the configuration block, but it is a bit frustrating that there is not clear guidance with examples on using document properties within an automation services configuration block (as far as I can see), despite this being a feature that seems to be described in community/help documents. 

[David Boot-Olazabal]

Hi Pamir,

Sorry for my late reply.

Good to hear you have found a workaround.
I would also encourage you to create an enhancement request though. If you feel there is a gap in the documentation about this point, it would be good to request more clarity in the product information. You can create such a request via https://ideas.spotfire.com/.

I'll also flag this with my colleagues as a part of the documentation that could be described more extensively.

Kind regards,

David