Difference between two calculated columns
Answered
In my report I have two columns: EV this month and EV next month what i want to do is add another column which will return the difference between these two columns.
My issue is that both these columns results are returned from advanced functions and therefore I cannot see a way of adding a further advanced function to get the difference.
Please help
Files:
Capture.PNG
The same question 
Hi Duncan,
what you can do in that situation is to use the feature called "Use as View" which you can find in the Report Settings tab of the Save Report dialogue.
In other words, when you save your current report as a view, then you can make a new report based on that view, and those 2 columns that were created by using an Advanced Function will just appears as regular columns, so you'll be able to apply a new Advanced Function to them.
I hope that helps you meet your reporting requirements, if not, then please let us know.
regards,
David
Hi Duncan,
what you can do in that situation is to use the feature called "Use as View" which you can find in the Report Settings tab of the Save Report dialogue.
In other words, when you save your current report as a view, then you can make a new report based on that view, and those 2 columns that were created by using an Advanced Function will just appears as regular columns, so you'll be able to apply a new Advanced Function to them.
I hope that helps you meet your reporting requirements, if not, then please let us know.
regards,
David
Hi David
Thank you for that, that looks like it should work however I have other issues with the report not returning values so I’ll get that sorted and then try your solution.
Thank you
Duncan Greenaway | Group Management Accountant
Scott Brownrigg | St. Catherine's Court, 46-48 Portsmouth Road, Guildford, Surrey GU2 4DU
T: +44 (0)1483 568 686
London | New York | Singapore | Hong Kong | Moscow | Cardiff | Edinburgh | Guildford
www.scottbrownrigg.com
Click here to download the Scott Brownrigg App for free from the App Store.
From: Yellowfin Support [mailto:support@yellowfin.bi]
Sent: 27 November 2017 04:24
To: Duncan Greenaway
Subject: New Comment in "Difference between two calculated columns"
Hi David
Thank you for that, that looks like it should work however I have other issues with the report not returning values so I’ll get that sorted and then try your solution.
Thank you
Duncan Greenaway | Group Management Accountant
Scott Brownrigg | St. Catherine's Court, 46-48 Portsmouth Road, Guildford, Surrey GU2 4DU
T: +44 (0)1483 568 686
London | New York | Singapore | Hong Kong | Moscow | Cardiff | Edinburgh | Guildford
www.scottbrownrigg.com
Click here to download the Scott Brownrigg App for free from the App Store.
From: Yellowfin Support [mailto:support@yellowfin.bi]
Sent: 27 November 2017 04:24
To: Duncan Greenaway
Subject: New Comment in "Difference between two calculated columns"
Big Dave
Thank you sorted out my report and it works brilliantly.
Ta
Duncan Greenaway | Group Management Accountant
Scott Brownrigg | St. Catherine's Court, 46-48 Portsmouth Road, Guildford, Surrey GU2 4DU
T: +44 (0)1483 568 686
London | New York | Singapore | Hong Kong | Moscow | Cardiff | Edinburgh | Guildford
www.scottbrownrigg.com
Click here to download the Scott Brownrigg App for free from the App Store.
From: Yellowfin Support [mailto:support@yellowfin.bi]
Sent: 27 November 2017 04:24
To: Duncan Greenaway
Subject: New Comment in "Difference between two calculated columns"
Big Dave
Thank you sorted out my report and it works brilliantly.
Ta
Duncan Greenaway | Group Management Accountant
Scott Brownrigg | St. Catherine's Court, 46-48 Portsmouth Road, Guildford, Surrey GU2 4DU
T: +44 (0)1483 568 686
London | New York | Singapore | Hong Kong | Moscow | Cardiff | Edinburgh | Guildford
www.scottbrownrigg.com
Click here to download the Scott Brownrigg App for free from the App Store.
From: Yellowfin Support [mailto:support@yellowfin.bi]
Sent: 27 November 2017 04:24
To: Duncan Greenaway
Subject: New Comment in "Difference between two calculated columns"
that's great new Duncan! Thanks for letting us know.
regards,
David
that's great new Duncan! Thanks for letting us know.
regards,
David
Hello David,
I tried to do this but cannot see the option "Use as View" in the Report settings tab... I'm using YF v 9.2
Can you help me?
Thanks,
Diogo
Hello David,
I tried to do this but cannot see the option "Use as View" in the Report settings tab... I'm using YF v 9.2
Can you help me?
Thanks,
Diogo
Hi Diogo,
I have just tested this and the option is still available as shown below:
If you are using a cross-tab it may be greyed-out. If the option does not appear as above, please check the Role Settings for your current role under Data Sources & Views and see that the Report as a Datasource is checked:
Let me know how you go with this info and we can proceed from there as needed.
Cheers,
Neal
Hi Diogo,
I have just tested this and the option is still available as shown below:
If you are using a cross-tab it may be greyed-out. If the option does not appear as above, please check the Role Settings for your current role under Data Sources & Views and see that the Report as a Datasource is checked:
Let me know how you go with this info and we can proceed from there as needed.
Cheers,
Neal
Thanks Neal, I did not the box ticked.
I am now able to save reports as views. Will test this and see if it covers my needs.
Best regards,
Diogo
Thanks Neal, I did not the box ticked.
I am now able to save reports as views. Will test this and see if it covers my needs.
Best regards,
Diogo
Hi Diogo,
Glad to hear we got you on the right path for now, please let me know if you need any further help with this.
Cheers,
Neal
Hi Diogo,
Glad to hear we got you on the right path for now, please let me know if you need any further help with this.
Cheers,
Neal
Hello Neal,
I'm afraid I have not managed to get this to work and I suspect there is a bug in the software (we always think like this isn't it?...).
Here is what I'm trying to do:
In fish farming one important KPI is FCR (Feed conversion ratio). It is the kg of feed you need to produce 1kg of fish. Easy to calculate: just divide the total amount (in kg) of feed you used by the total amount of fish you produced.
What I am doing is a report that shows monthly performance for a fish farm and calculates both the monthly FCR and the cumulative FCR.
So, I use subqueries to get the data for each tank at the start and end of each month as well as the aggregated data for each day. I get the kgs of fish in a tank at the end and start of the month (subtract this and I get the kgs produced) and from the master query I get the sum of the daily kgs fed, so the total feed. I divide one by the other and get FCR. So far so good.
In separate columns I use advanced functions to calculate the cumulative total of feeds from month to month (a) and the cumulative kgs produced from month to month (b). Because both are advanced calculations I can not use them again in calculations to get the cumulative FCR (a divided by b). So I saved this as a view and when I got the values again the cumulative values were way off the values I had in my report (thousands or millions of times bigger!). So I cannot, even with this view, get the cumulative FCR.
Note that for each report I am filtering data from thousands of batches of fish. I have a feeling that the cumulative values in the view are not being filtered.
Anyway, the issue for me is that this cumulative FCR is very important for farmers... I really want this to work
Any suggestion?
Thanks
Diogo
Hello Neal,
I'm afraid I have not managed to get this to work and I suspect there is a bug in the software (we always think like this isn't it?...).
Here is what I'm trying to do:
In fish farming one important KPI is FCR (Feed conversion ratio). It is the kg of feed you need to produce 1kg of fish. Easy to calculate: just divide the total amount (in kg) of feed you used by the total amount of fish you produced.
What I am doing is a report that shows monthly performance for a fish farm and calculates both the monthly FCR and the cumulative FCR.
So, I use subqueries to get the data for each tank at the start and end of each month as well as the aggregated data for each day. I get the kgs of fish in a tank at the end and start of the month (subtract this and I get the kgs produced) and from the master query I get the sum of the daily kgs fed, so the total feed. I divide one by the other and get FCR. So far so good.
In separate columns I use advanced functions to calculate the cumulative total of feeds from month to month (a) and the cumulative kgs produced from month to month (b). Because both are advanced calculations I can not use them again in calculations to get the cumulative FCR (a divided by b). So I saved this as a view and when I got the values again the cumulative values were way off the values I had in my report (thousands or millions of times bigger!). So I cannot, even with this view, get the cumulative FCR.
Note that for each report I am filtering data from thousands of batches of fish. I have a feeling that the cumulative values in the view are not being filtered.
Anyway, the issue for me is that this cumulative FCR is very important for farmers... I really want this to work
Any suggestion?
Thanks
Diogo
Hi Diogo,
Given the specifics of your request, I have created a private ticket for us to dig into this further.
Cheers,
Neal
Hi Diogo,
Given the specifics of your request, I have created a private ticket for us to dig into this further.
Cheers,
Neal
Replies have been locked on this page!