Parent-Child Sales Aggregation Issue

Raveena_Mariappan
10 days ago
2replies
Answered

Hi Team,

Lets say we have sales at parent level in base dataset1 and same is assigned to the corresponding children in dataset2. The total sales should remain 60 even when displayed at child level but Pyramid aggregates the sales for each row and shows 120.

Is there a workaround/steps to maintain the aggregation grand total at parent level even when we have both Parent and child on the same grid visual?

DataSet1

Parent	Sales
A	10
B	20
C	30

Dataset 2

Parent	Child	Sales
A	1	10
A	2	10
B	3	20
B	4	20
C	5	30
C	6	30

- VP Product Management
- Ian_Macdonald
- 4 days ago
- Reported - view
Hi

Firstly, I would question your data modelling here. Presented with your dataset 2 as a table, all database systems will return 120 for the total Sales. How could it do otherwise?

Secondly, you use the terms Parent / Child. Do you mean a Parent / Child Hierarchy? If so there are options for how to aggregate the data in your model using unary operators, see Help for details.

Thirdly, if the data exists as two tables, then reversing the direction of join, from dataset 2 to dataset 1, WILL produce the correct results, but it may not be correct for other situations. You'll also lose the relationship between parent and child:

Hope that helps.

Ian
- Immanuel_Gem
  
  3 days ago
  
  Reported - view
  HI
  Let’s say we have a single DB table that has sales data at the state level and population data at the city level and is pulled into PYRAMID using the direct query model (CUSTOM QUERY TABLE). Now we want to show both state and city level information in the same grid visual. Here the population data can roll up and give the right result, but the sales data should ignore the city level and aggregate based on the state level. For example, State A has made sales of $10; hence, cities 1 and 2 are assigned with the same sales value in the DB table. This logic holds true for other states and cities as well. While computing the grand total for sales, the actual output should ideally be 60 since we need to ignore the city; however, the data rolls up and gives 120 as sales. Could you please share inputs on how this can be achieved?
  
  DB DATA (Single Table)
  
  ACTUAL PYRAMID OUTPUT
  
  EXPECTED OUTPUT