I have a dataset that gives me cases (boxes) and units processed by a group of employees.
I want to calculate how many units per case there are. I want it to be granular to the employee level so I can know if they had good (many units) or bad (very few units) boxes.
The unit_type “EACH” is for units, while the unit_type “Case” is for the cases/boxes. I have also a date field which I use for filtering. Also, I want it to calculate it by the syze_category as “Total” as it also offers the granular data for the different sizes of units. How can I create a formula that does this? I have tried the following but it gives me a blank result when putting it next to the employees:
sumIf({unit_count},{unit_type}=‘EACH’ AND {size_category}=‘Total’)/sumIf({unit_count},{unit_type}=‘Case’ AND {size_category}=‘Total’)
I have also tried with sumovers with ifelse but I get no luck.
Hello @mguleal, I think we should be able to resolve this with sumOver calculations and nested ifelse statements. I’ll write out a possible solution below, and we can try to debug the result until we have a working solution. I will also include partitions for the employee (you may need to change the name of that field) since you want it at the employee level.
You are currently experiencing issues with the sumIf functions because it is only referencing the data returned on each individual row of the table. That means, you will never have a scenario where the statements of the numerator and denominator are true. Utilizing sumOver will allow the calculation to check every row value that the employee is linked to and should provide a result closer to your desired solution.
Let me know if that provides the answer you are looking for, thank you!
I’m marking Dylan @DylanM 's response as solution here. If you are still facing any issues after trying this out, please create a sample in Arena showcasing your issue with sample data. This will allow community members to help your further.
