Category
Report Builder
Product Version
7.3
creating weighted totals
Answered
Below is an excerpt from my data. I'm trying to do weighted averages on data by date. So if we have 1 job that runs at 50% of it's goal for 2 hours and another job that runs at 100% of it's goal for 6 hours then I get a overall of 87.5%.
I can see this happening several ways but I'm unsure how or if possible to do it in yellowfin.
Files:
Untitled.png
The same question 
I think I got a solution with a sub-query I can get total run time then do a bunch of advanced functions to get the numbers needed. Then when I go to my chart those numbers will be added which finally returns what I need. Let me know if you can think of an easier way.
I think I got a solution with a sub-query I can get total run time then do a bunch of advanced functions to get the numbers needed. Then when I go to my chart those numbers will be added which finally returns what I need. Let me know if you can think of an easier way.
Hi Chad,
Thank you for getting in touch. I am going to assume that you want to weight by % of total representation.
In this example, I am weighting the Invoiced amount, by the count of Athletes associated with that invoice, over the month.
Create a basic append sub-query, joining only on the value that you wish to weight over. In this example, I have joined on "month=month". This will allow us use the sub-total over that time period in the final calculation (see the red arrows).
The new weighted values are:
The weighted average for this time period is the sum of all of the weights.
For most use cases this will only be the setup. It will be necessary to then save this report as a view, and create a second report on top of this which aggregates these weights.
Does this sound like what you are going for? Please let me know if this does not meet your needs (and why) and we can explore alternatives.
Regards,
Nathan
Hi Chad,
Thank you for getting in touch. I am going to assume that you want to weight by % of total representation.
In this example, I am weighting the Invoiced amount, by the count of Athletes associated with that invoice, over the month.
Create a basic append sub-query, joining only on the value that you wish to weight over. In this example, I have joined on "month=month". This will allow us use the sub-total over that time period in the final calculation (see the red arrows).
The new weighted values are:
The weighted average for this time period is the sum of all of the weights.
For most use cases this will only be the setup. It will be necessary to then save this report as a view, and create a second report on top of this which aggregates these weights.
Does this sound like what you are going for? Please let me know if this does not meet your needs (and why) and we can explore alternatives.
Regards,
Nathan
Nathan
After seeing where you are going and what my solution was it seems they are similar so I'm assuming this is the only way. Appreciate the time.
Thanks
Chad
Nathan
After seeing where you are going and what my solution was it seems they are similar so I'm assuming this is the only way. Appreciate the time.
Thanks
Chad
Hi Chad,
I had not read your follow-up when I posted that. It does sound like you are already going down that path.
My only suggestion is rather than use "a bunch of advanced functions", it should be possible to consolidate these into one or two calculated fields.
Recently, several other clients have expressed interest this implementing this functionality. I believe that a single advanced function could easily accomplish this, and would save a substantial amount of sub-query mess, so I will raise this as an idea.
Regards,
Nathan
Hi Chad,
I had not read your follow-up when I posted that. It does sound like you are already going down that path.
My only suggestion is rather than use "a bunch of advanced functions", it should be possible to consolidate these into one or two calculated fields.
Recently, several other clients have expressed interest this implementing this functionality. I believe that a single advanced function could easily accomplish this, and would save a substantial amount of sub-query mess, so I will raise this as an idea.
Regards,
Nathan
Nathan
As suggested I got it down to 1 calculated field. I was unaware you could use a sub query field in a calculated field. Unaware but never thought about it either.
Thanks for the advice.
Nathan
As suggested I got it down to 1 calculated field. I was unaware you could use a sub query field in a calculated field. Unaware but never thought about it either.
Thanks for the advice.
Hi Chad,
No problem, there are a lot of things in Yellowfin that you just have to stumble across to know they exist.
Here is the Community "Idea" I have created for this:
http://community.yellowfin.bi/topic/advanced-function-weighted-average
I am going to close this ticket, but if you get a chance to "like" that Idea, it will increase the probability that the product team will select it for future inclusion.
Regards,
Nathan
Hi Chad,
No problem, there are a lot of things in Yellowfin that you just have to stumble across to know they exist.
Here is the Community "Idea" I have created for this:
http://community.yellowfin.bi/topic/advanced-function-weighted-average
I am going to close this ticket, but if you get a chance to "like" that Idea, it will increase the probability that the product team will select it for future inclusion.
Regards,
Nathan
Replies have been locked on this page!