creating weighted totals

Chad Miller shared this question 2 years ago
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.

Comments (6)

photo
1

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.

photo
1

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).

869a69089819c8b6db153363e917853e

The new weighted values are:


  1. Invoiced Amount * Count Athlete ID(master query) / Count Athlete ID(sub query)

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

photo
1

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

photo
1

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

photo
1

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.

photo
1

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