Making Calculations Based on other Calculations
24 May, 2012
Hi All,
I am trying to figure out the best way to come up with the following statistic:
We have data for bus ridership that has a date/time and the number of people who got on/off of a bus. We have created two calculated fields - 'AM Boardings' and 'PM Boardings'. This was just a simple calculation where we summed the boardings if the time value was less than 12pm - for AM boardings, and summed the boardings if the time value was greater than or equal to 12pm for PM boardings.
These values were then placed in a table and grouped for each month of the year.
Now for the tricky part... We can 'Count Distinct' the number of days in each month that people got on the bus and this value is always less than the total number of days in a month. We would like to calculate the 'average number of AM boardings and PM boardings for each month'. This is somehow taking the calculated AM (or PM) Boardings field and dividing it by the 'Count Distinct Days' field.
Is this possible?
I have attached a screen shot of the current state of our report in case this doesn't make 100% sense.
I am trying to figure out the best way to come up with the following statistic:
We have data for bus ridership that has a date/time and the number of people who got on/off of a bus. We have created two calculated fields - 'AM Boardings' and 'PM Boardings'. This was just a simple calculation where we summed the boardings if the time value was less than 12pm - for AM boardings, and summed the boardings if the time value was greater than or equal to 12pm for PM boardings.
These values were then placed in a table and grouped for each month of the year.
Now for the tricky part... We can 'Count Distinct' the number of days in each month that people got on the bus and this value is always less than the total number of days in a month. We would like to calculate the 'average number of AM boardings and PM boardings for each month'. This is somehow taking the calculated AM (or PM) Boardings field and dividing it by the 'Count Distinct Days' field.
Is this possible?
I have attached a screen shot of the current state of our report in case this doesn't make 100% sense.
Hi Chris,
at the moment this can be accomplished by setting up the calculated fields at the View level using Freehand SQL Calculated Field or a Virtual Table.
There is an Enhancement Request (Task ID = 99293) logged for the ability to reference Calculated Fields from within Calculated Fields, so hopefully at some point in the near future this sort of thing can be done at the Report Builder level.
Regards,
David
at the moment this can be accomplished by setting up the calculated fields at the View level using Freehand SQL Calculated Field or a Virtual Table.
There is an Enhancement Request (Task ID = 99293) logged for the ability to reference Calculated Fields from within Calculated Fields, so hopefully at some point in the near future this sort of thing can be done at the Report Builder level.
Regards,
David
Is the implementation of this functionality done? I also need something similar to be done.
Hi,
Unfortunately this feature has not been implemented yet, and no ETA has been given.
Apologies for the inconvenience.
Regards,
David
Unfortunately this feature has not been implemented yet, and no ETA has been given.
Apologies for the inconvenience.
Regards,
David
The calculated field support in Yellowfin is probably the biggest issue for me in using it. The clunky way in which calculations have to be created and edited, and the lack of ability to base a calculated field on another calculated field makes the product considerably less usable and attractive.
I'm also struggling to create a custom field that I require.
I am using one of the Calculated Field Templates, to calculate an 'Age' field, based on 'DateOfBirth' field.
I then need to create further custom fields based on 'Age' (e.g. When Age is unknown or negative, display Unknown). This is not possible. As I don't know the SQL that was used to create the original 'Age' field, it is hard to re-write this to suite my needs.
Has this been addressed in Yellowfin 7.0? I'm about to install in the new year.
I am using one of the Calculated Field Templates, to calculate an 'Age' field, based on 'DateOfBirth' field.
I then need to create further custom fields based on 'Age' (e.g. When Age is unknown or negative, display Unknown). This is not possible. As I don't know the SQL that was used to create the original 'Age' field, it is hard to re-write this to suite my needs.
Has this been addressed in Yellowfin 7.0? I'm about to install in the new year.
Hi,
this was actually addressed in 6.3 since the May build this year, i.e. it is now possible to create a Calculated Field that is based upon another Calculated Field. And it will certainly also be in YF 7.
regards,
Dave
this was actually addressed in 6.3 since the May build this year, i.e. it is now possible to create a Calculated Field that is based upon another Calculated Field. And it will certainly also be in YF 7.
regards,
Dave
Hi Dave
I've just upgraded to YF7, but am still yet to see how I can create a calculated field from another calculated field.
[img]http://www.freespace.com.au/imagehosting/images/54583472227611483.jpg[/img]
Referring to the attached image; I have calculated field 'Age' (created using YF formula templates).
I have created a new calculated field, and am looking to base it on the field 'Age'. This field does not show up in the formula builder.
Where have I gone wrong?
Regards
I've just upgraded to YF7, but am still yet to see how I can create a calculated field from another calculated field.
[img]http://www.freespace.com.au/imagehosting/images/54583472227611483.jpg[/img]
Referring to the attached image; I have calculated field 'Age' (created using YF formula templates).
I have created a new calculated field, and am looking to base it on the field 'Age'. This field does not show up in the formula builder.
Where have I gone wrong?
Regards
Hi Samuel,
great news! Your calculated field created at the view-level can be made available to your calculated fields at the report-level by enabling the Calculations switch in the Access tab in the View as shown below:
regards,
Dave
great news! Your calculated field created at the view-level can be made available to your calculated fields at the report-level by enabling the Calculations switch in the Access tab in the View as shown below:
regards,
Dave
This is a great step forward!
I did a little playing, and it seems it currently works with Calculated Fields based off SQL Templates, but not yet Freehand SQL.
But either way, looking forward to further updates and having more freedom with these Calculated Fields.
Many thanks!
I did a little playing, and it seems it currently works with Calculated Fields based off SQL Templates, but not yet Freehand SQL.
But either way, looking forward to further updates and having more freedom with these Calculated Fields.
Many thanks!
Hi David,
Can you please confirm that this functionality is available in version 6.3
Our system info:
I cannot see the 'Access' tab within the view builder on calculated fields, although it is available on standard fields within the view.
Patrick
Can you please confirm that this functionality is available in version 6.3
Our system info:
I cannot see the 'Access' tab within the view builder on calculated fields, although it is available on standard fields within the view.
Patrick
Hi Patrick,
just had a look at the latest build of YF 6.3 and the Access tab is available to Calculated Fields:
What sort of Calculated Fields are they (freehand, formula etc.)? Which db are you using?
regards,
Dave
just had a look at the latest build of YF 6.3 and the Access tab is available to Calculated Fields:
What sort of Calculated Fields are they (freehand, formula etc.)? Which db are you using?
regards,
Dave
Hi David,
I used the formula builder to generate the calc fields. Do I need to use a different method?
Were accessing a SQL database. Apologies I didn't realise the screenshots duplicated in the last message. Our version infor below:
Patrick
I used the formula builder to generate the calc fields. Do I need to use a different method?
Were accessing a SQL database. Apologies I didn't realise the screenshots duplicated in the last message. Our version infor below:
Patrick
Hi Patrick,
no you shouldn't need to use a different method.
I can confirm that this functionality is available in YF 6.3 20130725. And there is no system configuration setting needed for the Access tab to be displayed for Calculated Fields.
The only way I could reproduce this issue of the Access tab not displaying was when my Calculated Field was in draft mode, or there was something wrong with the formula. I can see from your screenshot that your field is indeed Active, could you please check that its Formula is valid?
regards,
Dave
no you shouldn't need to use a different method.
I can confirm that this functionality is available in YF 6.3 20130725. And there is no system configuration setting needed for the Access tab to be displayed for Calculated Fields.
The only way I could reproduce this issue of the Access tab not displaying was when my Calculated Field was in draft mode, or there was something wrong with the formula. I can see from your screenshot that your field is indeed Active, could you please check that its Formula is valid?
regards,
Dave
Hi David,
I can confirm that the formula is fine:
Would it matter that these formulas are based on an outer join of two tables? Trying to fill in the blanks so that I can perform calculations.
Patrick
I can confirm that the formula is fine:
Would it matter that these formulas are based on an outer join of two tables? Trying to fill in the blanks so that I can perform calculations.
Patrick
Hi Patrick,
I have just been made aware that there was indeed one build in which that exact issue you are experiencing occurred (i.e. Access tab not displaying) and so you should definitely upgrade to the latest build and that will resolve this issue.
regards,
Dave
I have just been made aware that there was indeed one build in which that exact issue you are experiencing occurred (i.e. Access tab not displaying) and so you should definitely upgrade to the latest build and that will resolve this issue.
regards,
Dave