Why is my RANK function changing result when I perform Bulk Copy Action?
Hello,
In short, when I perform a Bulk Copy, my RANK function is changing and giving different result than what it should be.
Detail:
I have a raw data source import file that is a listing containing all of our retail locations and their respective royalty rate that they pay us. The majority of the locations have one royalty rate for the entirety of their franchise agreement. However, there are locations that have “steps” in rate at various points in time. In sum, a salon could have 1 constant rate, or as many as 5 steps in rate over time. The import file has one column for location, one column for rate, and one column for start date. Thus, you have a many to 1 scenario where a location could be listed multiple times if there was more than one step.
My goal was to have Anaplan dynamically update the royalty rate schedule upon upload of the data source with no manual input to the source file.
In Anaplan the retail locations are a list.
I performed a RANK function by location over royalty step start date. If X location had 2 steps, the earlier date would be ranked 1, and the next date would be ranked 2. This worked as intended.
Rather than using the built-in VERSIONS, I use a list and create my own versions. I use “forecast” as my rolling/current version in my list. I archive my forecast monthly to a different list member like "Forecacst-April" via a Bulk Copy. I copy/archive so I can compare previous iterations of my model to the current Forecast.
When I performed the Bulk Copy to copy Forecast into Forecast -April, my RANK function for the step schedule changed. In the example given above, upon Bulk Copy, the X location would be ranked 1 and then 3. It should be 2 (vs 3) like when I originally built the formula/module.
As this is change is occurring during an Action like Bulk Copy, I am at a loss for how to troubleshoot. Any info much appreciated. Thanks!
Answers
-
Hi, Are you using ranking group within your rank formula? It might be that it is ranking all instances. Adding version to your ranking group could solve this problem.
1 -
When you perform a Bulk Copy Action in Anaplan, the data in the model is updated. This can cause the results of the RANK function to change. This is because the RANK function is based on the order of the data in the set. When the data is updated, the order of the data may change, which can cause the results of the RANK function to change.
There are a few things you can do to prevent the results of the RANK function from changing when you perform a Bulk Copy Action:
- Use a stable sort on the data before you perform the Bulk Copy Action. This will ensure that the order of the data is not changed when the data is updated.
- Use a formula to rank the data instead of the RANK function. This will ensure that the results of the ranking are not affected by changes to the order of the data.
- Use a subset to limit the data that is affected by the Bulk Copy Action. This will ensure that only the data in the subset is updated, preventing the order of the data in the rest of the model from changing.
1 -
Thanks for the suggestions. However, I am still unsure how to incorporate the suggestions. Any step by step on your thoughts would be greatly appreciated. You did get me thinking that maybe I should not be using RANK, and I am currently exploring this thread for ideas to use (unsure if i am going down wrong path, we'll see)
That said, below is more detail on my specific scenario.
Pivot:
Here is an example filtered for one location. I import my CSV into the first five columns. In this example this location (#16900) has two steps in rate. #16900 only has two line items in the import. I am doing the RANK function over the start date and salon number. In below, it is correctly ranking this location as 1 & 2. When I bulk copy the 2 will change to a 3.
Unfiltered View:
0 -
Pyry, I think this may be what you were refering to, but below is how i solved my issue. I had to concatenate/group by version list and location list. now when i bulk copy, it copies correctly. thank you all for your help to get me in right direction!
1
