Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have multiple records per day of specific brands and departments. Each record is averaged for the day and listed in a straight table and a line chart.
The records are tests with the count of "Good" and "Error" tests, aggregated per hour.
Results is 1-(error/success)
goal is always 95%
Over under is (results - goal)
I have attached a spreadsheet with what I am looking for, which is:
The combined department 1/2/3 has a goal but is dependent on the results of the other ones.
For the combined department 1/2/3, the goal needs to be the product of the other three departments' results and 95%
Example, for May 1 for Brand 1, the Combined department goal would be: 99% x 98% x 96% x 95% (Dept 1 x Dept 2 x Dept 3 x 95%) = 87%. This will alleviate the burden on the combined department to meet a goal when the contributing departments can't make their results.
I hope this makes sense!!
How do I incorporate the results of a formula of one row into another row, and specifically this formula.
Thanks for any input you can provide.
Hi Susan,
If you have further questions on this please post them here so all may benefit!
Cheers,
Jason
OK, and wow - what a great solution.
How does it work?
How do I get the 4th line (the combined department) to show?
How do I use this value in other objects, like line charts, showing the trend of the errors vs. goal over time?
By all 4 products?
I will have more questions.......
Hi Susan,
How does it work? - err...not sure actually!! The use of exp() and log() is a bit beyond my mathematical understanding. That solution came from Stefan.
How do I get the 4th line (the combined department) to show? - it is showing in the pivot table (the Total line)
How do I use this value in other objects, like line charts, showing the trend of the errors vs. goal over time? Just use
Min(Aggr(exp(rangesum(above(log(1-Sum(errors)/Sum(successes)),0,RowNo())))*.95,date,brand,Department)) in whatever chart you like (see attached)
By all 4 products? If product is another dimension in you data model just add it in to the charts.
Hope this helps,
Jason
PS - please mark the correct answer for the benefit of all.
Hi Susan,
Please mark a correct answer so this thread appears solved.
Thanks,
Jason