Question on aggregations
I have a module with 4 dimensions - Version, Countries, Projects and Employees. Single line item is worked hours. Hours at employee, project and individual country level correct but not when aggregated at All Countries - the numbers don't look right, why? The aggregations seem fine at individual country level but not when rolled up to the top which is All Countries. Any suggestions welcomed.
Best Answer
-
Perfect! I would say wipe out the entire dataset in the module first by placing 0 in the formula bar and then removing that 0 in order to make it input field again & then load the data back again.
Now to your question: Let's debug what could be happening.
Let's say you had 10 countries in Country list which you added as a dimension in your target module after the data load had happened. What this exercise will do is that will distribute the values equally into all 10 countries.
Then you went back to the data set and included Country Column as well. Here is the catch, Unless and Until you include all the 10 countries in the data set, numbers in Anaplan which were there for all of the 10 countries will not be wiped out. Numbers for matched countries only will be updated and non matched countries will continue to show those junk fractional numbers. That's the reason you see Junk fractional numbers at the aggregated level and that's the reason when you get rid of these countries from the list, numbers matched.
Long Story Short - Wipe out the numbers from the module first and then upload it again. Use the entire country list and don't delete any list member if you need them in future
Hope that helps
Misbah
4
Answers
-
A couple of ideas for "worked hours":
- If it's meant to be summed, then make sure you have a SUM aggregation.
- If it's a formula then you'll need to think about how to aggregate that up country.
- If it's a formula that doesn't use LOOKUP or SUM then you can use "Formula" for your summary
- If it's a formula that does use LOOKUP or SUM then you'll need to create another line item that allows you to use ratio or formula as a summary, or just create another line item that equals worked hours, and use SUM on that for the summary.
Beyond that, you'll have to share more details about what you're seeing or a better description of what you mean by "doesn't seem correct".
Let's get you to the finish line.
1 -
Hi Jared,
Many thanks for your quick pointers and response. No worries have found the solution but just to share for the benefit of forum readers, the hours mentioned (both actual and forecast) are inputs so no formulas. Each project comprises several team members (APAC is diverse). For example, team members may come from Singapore, India, Australia etc. I was wondering why the hours didn't add up correctly and in fact had strange fractional results at the aggregate All Country level. The solution (please let me know if there is a better / different way) was to only have countries in my country list that matches the countries team members are based. For example, no Hong Kong in country list when no employee is based there. For some reason this causes a problem. I investigated by checking the country (e.g. HK) and found strange fractional numbers which were clearly not part of the input dataset. It looks like Anaplan does not but no sure why this happens - do you have any idea?
Kind regards,
Clarence
1 -
@ClarenceAndre It usually happens when the module is not created correctly with the proper dimensions. If you created your module with lets say 3 dimensions then uploaded the data and then added another dimension to the module all the numbers would have been equally distributed to the fourth dimension (which may result in fractional numbers all over the place).
But if that wasn’t the case then it should match the numbers even at the aggregted levels without removing the country from the dimension. If you remove the country then next time you upload the data you will have to update the country list first and then upload the data which will become a two step process.
4 -
Hi @Misbah thanks very much for the helpful reply. I created the (dummy) data. It is true I entered the country dimension later but I also went back to the dataset and created a column for country. Each team member is based in only one country. So it is strange there would be a fractional result at aggregate country level but when I removed the "additional countries", the fractional problem went away i.e. the numbers now add up correctly at the aggregate level. The reason why I even had "additional countries" in the first place is because I am thinking ahead when we might have team members based in those countries. But Anaplan doesn't seem to like that unless am doing something wrong?
Kind regards,
Clarence
0 -
Perfect, thanks @Misbah
0
