Cumulate a moving target
Hey Anaplanners! Hopefully a fun one and not just my own short-circuiting. I'm working through a capacity allocation rankcumulate, where my list items(SKUs) have a target for a month, and multiple sites that can produce units towards that target. My issue is that the cumulation is correctly grouping by SKU group (family) and site, but each site can't tell whether a SKU's target was met by a prior site, or skipped. I attempted a parallel calculation for target, but ended up with a circular reference as it's the output of the earlier rankings that determine the remaining target. I'm using stepwise, so I can "reset" my target each step, but the same SKU is target seeking multiple times per step. Here's my excel showing in the yellow the SKUs duplicating their targets in step 1, hopefully this gives a better visual than my current blueprint mode.
tldr; The challenge is that I have a capacity limit by P5 and site, but my target is by SKU and region. All help is appreciated :)
Answers
-
Ok, I will play your game…Now, I don't have Region, but the rest is the same except I flipped the SKU and Site on my axis…Instead of rankcumulate(), I used cumulate(). Also, I couldn't get the green highlight because that is a different formula at the summary level. Take a look and let me know your thoughts.
0 -
And because I wanted to get extra credit, I went ahead and did the Target 1 - Step 1 Output calc as well.
0 -
Rob, welcome! I knew I should've just tagged you. Okay YES this gets us to a standard moving target between steps—sorry I was not as clear that I need my target to move during step 1, so that when SKU 1 shows up at Site 2, my model knows Site 1 already reached the SKU goal, so my Step 1 Site 2 and 3 goal is now 0. I assumed the SKU cumulate was the best way to see that—That's the real intention of "SKU Target Cumulate" as I don't think there's another way to "see" the Step 1 Rank Cumulate from a SKU's perspective.
I want Sum at SKU = Target 1, that's the simplest way to explain it I think.
My workaround today was dividing Target 1 by valid sites to get a "Split Target" rather than a "Moving Target"…but that splits evenly vs using the Family Rank or SKU Rank to make sure a prioritized site takes on more load.
Third time's a charm, or did I scare you off?
0 -
No, you did not scare me off…I will send you an email and we can hash this out.
0 -
Just to resolve the thread: we confirmed this is a case for optimizer, as trying to do steps small enough to consistent re-calc the moving target would be quite burdensome, and still not as precise/accurate as a true optimization algorithm.
Thanks for the assist Rob!
1 -
@rachel.bisch my pleasure and I reached out to your BP, hopefully have you heard from him.
1
