Create a new Date Field using Freehans SQL
17 March, 2015
Hi Yellowfin support,
I'm having an issue on creating a new field in a view. I'm using SQL Server database.
I need to create a Date field to match a Date field in a subquery.
The new field has to add one month to an existing field (field called 'Period Processed' in the DW. Here the strarting field, it's a Date field:
When I create the new field as a Freehand SQL, using the type DATE, the field is created as a Varchar:
and cannot be used in the subquery:
If I force the conversion to a DATETIME, the field is created as a TImestamp, and can't be used in the subquery either:
Any idea on how can I add one month to the Date field, avoiding conversion issues?
I know the solution I'm using on other databases, as explained in that post, but what about SQL server?
http://www.yellowfinbi.com/YFForum-Conversion-of-Data-type-to-date-?thread=153726#p7
Thanks a lot,
Francesca
I'm having an issue on creating a new field in a view. I'm using SQL Server database.
I need to create a Date field to match a Date field in a subquery.
The new field has to add one month to an existing field (field called 'Period Processed' in the DW. Here the strarting field, it's a Date field:
When I create the new field as a Freehand SQL, using the type DATE, the field is created as a Varchar:
and cannot be used in the subquery:
If I force the conversion to a DATETIME, the field is created as a TImestamp, and can't be used in the subquery either:
Any idea on how can I add one month to the Date field, avoiding conversion issues?
I know the solution I'm using on other databases, as explained in that post, but what about SQL server?
http://www.yellowfinbi.com/YFForum-Conversion-of-Data-type-to-date-?thread=153726#p7
Thanks a lot,
Francesca
Hi Francesca,
You may find that it's actually easier to just link the filters in the report and adjust from there, rather than trying to create a specially written Freehand SQL field for this purpose.
In your scenario you would add the date filter to both queries, then in the filter setup of the second query, change the filter operator to be Link to Filter.
From here, you need to click on Define Link. What this does is sets the second filter to reference the date defined for the first filter. Here you can leave the operator to be Same Operator, then point to your first filter in the Link to Filter drop down.
Next, you just need to specify how you wish to manipulate the date range. In your scenario you + 1 Month. Then Submit.
Now your users can pick the month for the first query and Yellowfin will automatically add a month to the start and end date of that for the second query.
Regards,
Teresa
You may find that it's actually easier to just link the filters in the report and adjust from there, rather than trying to create a specially written Freehand SQL field for this purpose.
In your scenario you would add the date filter to both queries, then in the filter setup of the second query, change the filter operator to be Link to Filter.
From here, you need to click on Define Link. What this does is sets the second filter to reference the date defined for the first filter. Here you can leave the operator to be Same Operator, then point to your first filter in the Link to Filter drop down.
Next, you just need to specify how you wish to manipulate the date range. In your scenario you + 1 Month. Then Submit.
Now your users can pick the month for the first query and Yellowfin will automatically add a month to the start and end date of that for the second query.
Regards,
Teresa
Hi Teresa,
thanks a lot for your help and quickly reply!
Unfortunately the solution will solve just part of my my situation! :(
Here the filter I need to apply:
- Master Query: Active customers at the end of the Current Month (ex March 2015)
- Subquery: Active customers at the beginning of the Current Month, that means searching for the Active customers at the end of Previous Month (ex Feb 2015)
The filtering is fine, but I need to show both results as they are referred to March 2015!
That's for merging the results in the Main query:
Thanks a lot!
Francesca
thanks a lot for your help and quickly reply!
Unfortunately the solution will solve just part of my my situation! :(
Here the filter I need to apply:
- Master Query: Active customers at the end of the Current Month (ex March 2015)
- Subquery: Active customers at the beginning of the Current Month, that means searching for the Active customers at the end of Previous Month (ex Feb 2015)
The filtering is fine, but I need to show both results as they are referred to March 2015!
That's for merging the results in the Main query:
Thanks a lot!
Francesca
Hi Francesa,
To solve this what you would need to do is join the data on a different field that is common to both months. You can then hide this field from display so you don't know it's there. From there you could add the Month label to the current month query, but don't add it to the other sub query.
Regards,
Teresa
To solve this what you would need to do is join the data on a different field that is common to both months. You can then hide this field from display so you don't know it's there. From there you could add the Month label to the current month query, but don't add it to the other sub query.
Regards,
Teresa