Union Query – not returning calculated field value
11 September, 2014
Hi Folks
I have created a calculated field in a union query that is not returning the intended value. I'II step you through this as follows:
1. Data Table screenshot - the name of the calculated field, called 'Contracted Suppliers - GSSP signatories %'is appearing in the 1st column of the table but a corresponding value is not showing in the 2nd column. The value that should be returned is shown in red and this is derived from dividing 'Contracted Suppliers - GSSP signatories (number)' by 'Contracted Suppliers (number)' - these fields and there values are in the table.
2. Union Query page screenshot - this shows the content of the query
3. UnionQueryCalcField - this screenshot shows the calculation, note SQL is valid message.
4.MasterQueryFilter and Union1Filter - these 2 screenshots show the filters used
Please advise why this the intended value is not pulling through.
Also, how would I format the intended value as a percentage
Many Thanks
Christine
I have created a calculated field in a union query that is not returning the intended value. I'II step you through this as follows:
1. Data Table screenshot - the name of the calculated field, called 'Contracted Suppliers - GSSP signatories %'is appearing in the 1st column of the table but a corresponding value is not showing in the 2nd column. The value that should be returned is shown in red and this is derived from dividing 'Contracted Suppliers - GSSP signatories (number)' by 'Contracted Suppliers (number)' - these fields and there values are in the table.
2. Union Query page screenshot - this shows the content of the query
3. UnionQueryCalcField - this screenshot shows the calculation, note SQL is valid message.
4.MasterQueryFilter and Union1Filter - these 2 screenshots show the filters used
Please advise why this the intended value is not pulling through.
Also, how would I format the intended value as a percentage
Many Thanks
Christine
Hi Christine,
Thanks for all the screenshots and apologies for the delay.
Can I please get a snippet of the raw data this report is pulling?
Also, are any of the fields in your calc field actually calc fields themselves?
Once I have this, I can setup the same table here in my DB and create the same report.
Please let me know if you have any issues getting this data across.
Even exporting to a CSV would be easy for us to import in.
Just one last thing, which version of Yellowfin are you currently using? Is it the latest 7.0, 7.1 ?
As we do know there was a little issue with sub-queries just the other month which could also affect this report.
Thanks,
David
Thanks for all the screenshots and apologies for the delay.
Can I please get a snippet of the raw data this report is pulling?
Also, are any of the fields in your calc field actually calc fields themselves?
Once I have this, I can setup the same table here in my DB and create the same report.
Please let me know if you have any issues getting this data across.
Even exporting to a CSV would be easy for us to import in.
Just one last thing, which version of Yellowfin are you currently using? Is it the latest 7.0, 7.1 ?
As we do know there was a little issue with sub-queries just the other month which could also affect this report.
Thanks,
David
Hi David
Thanks for your response.
Last week we solved the issue of the union query calculated field value not showing. Per the attached doc called 'Report' you can see that a value of 18.4 is now showing for the appended row called 'Contracted Suppliers - % of GSSP signatories'.
The issue was with the actual formula on the calculated field...per attached screenshot called 'Calc Field formula') you can see that that SUM was added to front of each CASE statement and hey presto it worked.
The calculated value of 18.4 is actually a %, based on the other 2 numbers displayed in the report. Please advise as to how we format the union query so that the % sign is showing e.g. 18.4% rather than 18.4.
We are on YF V7.0 at present.
Thanks
Christine
Thanks for your response.
Last week we solved the issue of the union query calculated field value not showing. Per the attached doc called 'Report' you can see that a value of 18.4 is now showing for the appended row called 'Contracted Suppliers - % of GSSP signatories'.
The issue was with the actual formula on the calculated field...per attached screenshot called 'Calc Field formula') you can see that that SUM was added to front of each CASE statement and hey presto it worked.
The calculated value of 18.4 is actually a %, based on the other 2 numbers displayed in the report. Please advise as to how we format the union query so that the % sign is showing e.g. 18.4% rather than 18.4.
We are on YF V7.0 at present.
Thanks
Christine
Hi Christine,
you will need to use the Suffix setting in the Column Formatting feature as described in this wiki page.
Please let us know if that doesn't do what you want.
regards,
Dave
you will need to use the Suffix setting in the Column Formatting feature as described in this wiki page.
Please let us know if that doesn't do what you want.
regards,
Dave
Hi Dave
Thanks for your response. The suffix format option adds a % sign to every value in the column, we want the % sign to apply to only 1 vaue in the column. The attached screenshots illustrate this:
SS1 - there are 3 values in the column, the top value is a % of the other 2 values
SS2 - using the suffix format option the % sign is added to all 3 values though.
Really we need to be able to format by row.
What can be done to achieve the outcome we are seeking?
Thanks
Christine
Thanks for your response. The suffix format option adds a % sign to every value in the column, we want the % sign to apply to only 1 vaue in the column. The attached screenshots illustrate this:
SS1 - there are 3 values in the column, the top value is a % of the other 2 values
SS2 - using the suffix format option the % sign is added to all 3 values though.
Really we need to be able to format by row.
What can be done to achieve the outcome we are seeking?
Thanks
Christine