I’m new to QS and still learning how to deal with calculations.
I’m struggling to come up with an approach to calculate % towards target goal for each agent with single calculated field when each agent has his own target goal number (#2 questions on the list for calculated fields below).
Also, it is mandatory requirement to validate that each agent is not only meeting a target goal of reserved boxes across all Cities, but their reserved boxes are equally distributed at least between 2 shelves in each City.
Here is a summary of entry criteria:
-
Each City can have between 2-5 shelves, 2 is a mandatory standard for each City. There are 100+ different cities
-
Each Shelf always has 12 boxes (fixed number), these boxes can be unreserved or reserved to different agents
-
Each Agent may have different number of boxes reserved within each City, but this number is expected to meet a target goal eventually
-
Each Agent has his own target goal of reserved boxes for any City with mandatory requirement that their reserved boxes are distributed between shelves (not on single shelf)
- agent_1 to have 8 (or more) reserved boxes (total) in each City distributed between all (2 minimum) shelves in this City
- agent_2 to have 4 (or more) reserved boxes (total) in each City distributed between all (2 minimum) shelves in this City
- agent_3 to have 3 (or more) reserved boxes (total) in each City distributed between all (2 minimum) shelves in this City
- agent_4 to have 5 (or more) reserved boxes (total) in each City distributed between all (2 minimum) shelves in this City
-
Distribution pattern for reserved boxes should be:
- if City has 2 mandatory shelves, reserved boxes for each agent should be equally distributed between both (50/50) and any excessive reserved boxes (above 100% goal) can be on any of these shelves
- if City has >2 shelves, 50/50 distribution requirement for first 2 shelves should be met and any excessive reserved boxes should be distributed at least 1 box minimum to each extra shelf. For example, if agent_2 has 5 reserved boxes in City_B with 3 shelves, they should be 2+2+1 (where 2+2 are on first mandatory shelves and 1 box expected to be on 3rd shelf), if agent_2 has 7 reserved boxes in City_B the distribution is expected to be 2+2+1 and the rest of excessive boxes can sit on any shelf out of these 3. If City_C has 4 shelves and agent_2 has 7 reserved boxes in this city, to meet a goal they need to have them distributed as 2+2+1+1 and 1 remaining box can be on any shelf out of these 4 available in this City_C.
I’m trying to create the following calculation fields that can be used across agents with different target goals if possible:
-
Calculate total of reserved boxes for each agent per city. I think I can achieve it with: countOver({box_ID}, [{Agent_ID}], PRE_AGG), it seens to show accurate numbers across agents in single visual with this formula
-
% towards goal by agent in each City. it should take into account different goals for each agent, i.e. 8 for agent_1 and 4 for agent_2, etc. I can only calculate % towards goal through dedicated calculated field for each agent which doesn’t allow me to use them in meaningful way in single visual
-
Create some calculated metric which will indicate that reserved boxes distribution by agent across shelves is met too based on their individual goals. I can’t come up with a formula to validate boxes distribution across shelves within city towards target per agent, even if I go with dedicated calculated fields for each agent with their own target goal number.
Please let me know how to approach these calculations to create some meaningful way to measure progress towards goal of each agent across each City, ideally, within same visual.
