Dividing two columns (subqueries)
6 June, 2013
Hello:
I am trying to create a report showing financial results by account with the following four columns: 1) 2012 data; 2) 2013 data annualized (taking 4 months worth of data and multiplying the results by 3); 3)the difference between columns 1 and 2; and 4) the percentage difference between 1 and 2.
I've managed to create columns 1 and 2 successfully, as shown here:
To create column 2, I used an append subquery which used a calculated field which took the sum of the results and multiplied them by 3 in order to annualize it. Where I'm stuck is creating the other two columns. Basically, I would like to perform basic operations (subtraction and division) on two columns, one of which is the result from a master query and the other a subquery. Is this possible to do in Yellowfin? I've tried a few different methods with no luck.
Thank you in advance.
Adam
I am trying to create a report showing financial results by account with the following four columns: 1) 2012 data; 2) 2013 data annualized (taking 4 months worth of data and multiplying the results by 3); 3)the difference between columns 1 and 2; and 4) the percentage difference between 1 and 2.
I've managed to create columns 1 and 2 successfully, as shown here:
To create column 2, I used an append subquery which used a calculated field which took the sum of the results and multiplied them by 3 in order to annualize it. Where I'm stuck is creating the other two columns. Basically, I would like to perform basic operations (subtraction and division) on two columns, one of which is the result from a master query and the other a subquery. Is this possible to do in Yellowfin? I've tried a few different methods with no luck.
Thank you in advance.
Adam
Hi Adam,
I think the problem here is thinking that the calculations should be done on the sub-queries.
When using calculated fields & sub-queries, all these calculations should be created on the master query, you can then drag these into the relevant sub-query.
So in your case you will have your master query with the 2012 filter applied and the sub-query with 2013 data.
Then you create your calculations on your master query (and using the fields returned from the sub-query) .
Please let us know how you go.
Regards,
David
I think the problem here is thinking that the calculations should be done on the sub-queries.
When using calculated fields & sub-queries, all these calculations should be created on the master query, you can then drag these into the relevant sub-query.
So in your case you will have your master query with the 2012 filter applied and the sub-query with 2013 data.
Then you create your calculations on your master query (and using the fields returned from the sub-query) .
Please let us know how you go.
Regards,
David
Hi David - Thank you for your response. When I try to create a calculated field in the master query, only the master query fields are displayed as options. The metric from the subquery is not displayed. Am I doing something incorrectly?
Hi,
No problems.
Are your metrics actually calculated fields?
You won't be able to use calculated fields in another calculated field, in Yellowfin 6.2.
We only support this function in Yellowfin 6.3 + versions.
Regards,
David
No problems.
Are your metrics actually calculated fields?
You won't be able to use calculated fields in another calculated field, in Yellowfin 6.2.
We only support this function in Yellowfin 6.3 + versions.
Regards,
David
David, the metric in the subquery calculates the first four months of 2013 and multiplies it by 3 to annualize it.
If we can't do it the way you say we can't, what do we do?
If we can't do it the way you say we can't, what do we do?
Hi Kelly,
Is the reason why you cannot do it, related to not have a 'qaurter' date field?
If not, you can actually convert any date field to another date range using the date hierarchy calculated fields.
Also, if you'd like to use a calculated field within a calculated field then this is very easy to do in 6.3, however if you are on an earlier version then perhaps the best way would be to create the calculated field at the view level so that it will be available as a regular column at the report level, either by using a Freehand SQL Calculated field or by adding a Virtual Table containing the calculated field to the view.
I hope that helps with your report, if there are any further questions the please don't hesitate to contact us.
Regards,
Dave
Is the reason why you cannot do it, related to not have a 'qaurter' date field?
If not, you can actually convert any date field to another date range using the date hierarchy calculated fields.
Also, if you'd like to use a calculated field within a calculated field then this is very easy to do in 6.3, however if you are on an earlier version then perhaps the best way would be to create the calculated field at the view level so that it will be available as a regular column at the report level, either by using a Freehand SQL Calculated field or by adding a Virtual Table containing the calculated field to the view.
I hope that helps with your report, if there are any further questions the please don't hesitate to contact us.
Regards,
Dave
Yes we are on 6.2.
We do have a Quarter field as a dimension.
I have attempted to create the percent and difference filters at the view level but have not yet figured it out. I have successfully created formula filter for 2013 first quarter and one for 2012 as well (full year).
Thanks, Kelly
We do have a Quarter field as a dimension.
I have attempted to create the percent and difference filters at the view level but have not yet figured it out. I have successfully created formula filter for 2013 first quarter and one for 2012 as well (full year).
Thanks, Kelly
I was able to create calculated fields for % and $ difference but I don't know if it's right. SQL is valid.
I was not able to get 2013 Quarter 1 x 3 in a calculated field.
See attached.
Thank you.
I was not able to get 2013 Quarter 1 x 3 in a calculated field.
See attached.
Thank you.
Attachment did not work on previous comment. See below.
Hi Kelly,
I think what you're doing here directly relates to this post:
Add calculated columns to report .
Here are the problems I see with you're above fields;
1- Your date filter 'Transaction Date as Dimension' actually contains a calculation x3 . This is not valid SQL, you are not able to times a date value x 3.
2- Sum (amount) - Sum (amount)
I'm not sure why you would need to use this field at all, as it always going to return 0, unless these 'amount' fields actually came from different tables. If they do, you should probably rename these amount fields to be more descriptive.
However I think the other post actually contains detailed information on doing this at the report level using sub-queries.
Please let me know how you go.
Regards,
David
I think what you're doing here directly relates to this post:
Add calculated columns to report .
Here are the problems I see with you're above fields;
1- Your date filter 'Transaction Date as Dimension' actually contains a calculation x3 . This is not valid SQL, you are not able to times a date value x 3.
2- Sum (amount) - Sum (amount)
I'm not sure why you would need to use this field at all, as it always going to return 0, unless these 'amount' fields actually came from different tables. If they do, you should probably rename these amount fields to be more descriptive.
However I think the other post actually contains detailed information on doing this at the report level using sub-queries.
Please let me know how you go.
Regards,
David