Aggregating sales amount based on filter criteria


We have 3 modules where:


1. The Allocation module has Unique IDs(UID1), Time as a dimension and Attributes as Line items




2. The Sales Data has Unique IDs(UID2) as a dimension, Time, Attributes as Line items



3. Output Module which has UID1, UID2, Time as a dimension and Attributes as Line items

Screenshot (13).png


We have to allocate the amount in the allocation module into the sales module based on the sales quantity ratio in the sales module corresponding to the filter or joining criteria.

We have to aggregate the sales quantity amount based on the filter / join criteria mentioned above(AGG KEY = Company Code, Division, Sales Organization, Sales Office, Profit Component, District, IndustrySubSegment, ProductHierarchy).


Division, SalesOrg, CompanyCode, ProfitCenter -> should be an exact match in both of the modules (1 & 2) IndustrySubSegment, SalesOffice, District, ProductHierarchy, Material -> if a value is present in the allocation module then should be an exact match in the Sales module, else if this is missing in allocation module then consider all values for these attributes in the sales module


In the output module, we can't add other attributes as dimensions Since those are lists with millions of data.