Difference between two calculated columns

Duncan Greenaway shared this question 2 years ago
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

Comments (10)

photo
1

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

photo
1

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

1cf9107b99fafe76292e94d1c7719b9f 

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"

photo
1

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

8987a0b5fe47f6c29f4fb440661e9afc 

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"

photo
1

that's great new Duncan! Thanks for letting us know.

regards,

David

photo
1

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

photo
1

Hi Diogo,


I have just tested this and the option is still available as shown below:

fd287dd861a89d4ac05bcd4ed676351c

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:

31889d22bef5efebe6732917e8dece82

Let me know how you go with this info and we can proceed from there as needed.

Cheers,

Neal

photo
1

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

photo
1

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

photo
1

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

photo
1

Hi Diogo,

Given the specifics of your request, I have created a private ticket for us to dig into this further.

Cheers,

Neal