Dateadd in calculated column using MySQL Data connection

[Josie Dupuis 2]

I am connecting to an Aurora database and using a custom query on Spotfire Analyst 10. I created property control dropdownsfor reporting periods such as Previous Month/Quarter/Year. I created a calculated column to drive a filter for the report based on the drop downs, but the dateadd() function is not available for that connection type. I am relatively new to Spotfire and new to mySQL.. How do I work around this

 

#drspotfire

 

Thank you!

[Michael Kalada 2]

Are you doing a direct connect to the database keeping the data "in database" When you do that spotfire severely limits what you can do/what functions are available to you

This is the approach you would take for very large datasets that would take too long to load.

If your dataset is huge, and you need to keep it in database you can only perform certain functions in the database through a view or whatever.

If your dataset is not that huge, you would use infolinks to bring the data into your dxp and then you will have all native Spotfire functions available for your calculation.

Of course, you can take a hybrid approach as well, leaving the bulk of your data in-database but bringing over an aggregated table via info links.

or, your "custom query" is causing the problem in which case you just have to use info link parameters/filters etc or a view in your MySQL to achieve the same outcome

[Josie Dupuis 2]

Yes, I am doing a direct connect to the database using the Oracle Mysql connector .  I had tried creating an information link first, and had all the property controls working with the date calculations.  I was having difficulty manipulating the dataset so I  tried the custom query, losing the  functionality in the date calculation.  The hybrid approach may be my best bet - thank you!