Forum Discussion

benmac360's avatar
benmac360
New Contributor III
2 years ago

Incorrect Consolidated Data

Hello - we recently updated the stored values in the Percent Consolidation property in several large entity hierarchies, so there are 3 stored items with different time periods for each child. We found today that when we pull data using a YTD view, whether in the Excel add on or a cube view within the Windows application, the consolidated numbers on the parent do not match the children when the latest stored value is applied to the child data. However, we did find when the view is changed to Periodic the consolidated parent data matches the product of the child data and the current Percent Consolidation. Is this issue related to the Consolidation View on our Actual scenario? Its currently set to Periodic. If this helps, our Consolidation Algorithm Type is Standard.

Consolidation
Cubes
    • benmac360's avatar
      benmac360
      New Contributor III
      2 years ago

      Thanks Chul - the post was very helpful! 

      I did find out how its calculating the YTD amounts - OneStream is summing the product of the periodic amounts and the consolidation percentages for each period. Its showing the correct YTD and periodic amounts for every month we have actuals - so our remaining issue is for the remainder of the year (Jun-Dec) and only on the structures that were updated, there are derived amounts on the parents that are the sum of the periodic amounts for Jan-May. Our No Data Zero View for Non Adjustments on the Actual scenario is set to YTD, so the base and child members of the hierarchy are summing up to zero in Jun with the derived amounts, except for the parents where the percent consolidation stored item is applied. What would cause the YTD amount to not equal zero for Jun-Dec in this situation?

      • chul's avatar
        chul
        Contributor III
        2 years ago

        I'm not completely sure I understand your question - a screenshot of the situation would help if you're able to provide one.

  • benmac360's avatar
    benmac360
    New Contributor III
    2 years ago

     

    Here's a screenshot of our hierarchy validation that shows the actuals for June and each month thereafter: 

  • benmac360's avatar
    benmac360
    New Contributor III
    2 years ago

    Here's another screenshot of 2 quick views for the detail - the first shows the periodic amounts and a sum of these amounts in the next column. The second quick view shows the YTD amounts which tie to our hierarchy validation. You'll notice there's no data for June at the base member level which is correct - but starting at the parent where we made the consolidation percentage change is where these derived amounts are appearing for June-December. Before a force consolidation on these hierarchies would correct the problem for future months, but its not working here.

  • chul's avatar
    chul
    Contributor III
    2 years ago

    Is there a reason why you're trying to validate future months that haven't had loaded data? Or is this more of a "why is this data being calculated" question? I ask because you've stated that you've "corrected" the future months by force consolidating but it's no longer working.

    If it's "why is this data being calculated", my hunch is that the business rules you've got running are looking at the derived data and calculating ownership based on those values. So even though you haven't loaded data to June, the derived data shows a periodic change of 923k.

    If your issue is strictly presentation, you can return zero (or whatever value you want) for the future months in a CV using an XFBR rule or possibly conditional formatting (if it's a future month, text color = transparent).

    • benmac360's avatar
      benmac360
      New Contributor III
      2 years ago

      Yes, that's correct, its a question of why are we seeing these amounts in future months just for these 3 structures. I'll have our business rules reviewed, see if any recent changes were made. Thanks for your help!