12 Replies Latest reply on May 8, 2018 12:28 PM by sathish Chidambaram

    Mapping data from different sources & Hierarchy

    sathish Chidambaram

      Hi,

       

      I have a different situation here. ‘Planned sales’ & ‘Current Sales’ `are from Source 1 and they have multiple records for each proj. Source table 2 which have ‘Estimated sales’ for that year which will be static and updated every yr (.

       

      Need help in achieving below goal.

       

      1. Need to map the source 2 date respective with source 1 Cat/subcategory  and present the final data in Hierarchy order: Total, Group, Project, Category, Sub category as shown in Expected result below.  I tried to created new calculated filed and assign the values to Sub Category level,  ‘Estimated Sales’ values is not rolling up based on the hierarchy. For Sub category level, I seeing correct values but after rolling up to category/ project level its not summing up.
      2. Total should be displayed Project Level (only total sum of that proj), with category/ subcategory details for Group level and overall total.

       

       

      Attaching the mocked-up workbook. As shown below, need to display total for Cat 2, cat 3 and also the 'Estimated Sales' should be rolled-up.

      Ultimate goal is to allow users to see the values based on Group, Project level. If we can achieve this using parameters apart from Hierarchy, please suggest.

       

      I appreciate any help you can provide!

       

       

      Issue with Estimated Sales:

       

       

      Expected Result

       

      Project

      Category

      Sub Category

      Planned sales

      Current Sales

      Estimated Sales

      Total (Inclues  both group)

      Total

       

       

      105,119

      12,607

      200,254

       

      Cat 1

       

      19,587

      1,535

      37,084

       

      Cat 2

       

      21,705

      3,066

      29,667

       

       

      Subcat1

      7,632

      1,170

      11,125

       

       

      Subcat2

      14,073

      1,897

      18,542

       

      Cat 3

       

      21,061

      2,470

      51,918

       

       

      subcat3

      10,419

      558

      14,834

       

       

      subcat4

      10,642

      1,912

      37,084

      Group 1

      Group 1 Total

       

       

      71,623

      9,302

      119,880

       

      Cat 1

       

      15,881

      1,316

      22,200

       

      Cat 2

       

      9,929

      2,115

      17,760

       

       

      Subcat1

      4,380

      860

      6,660

       

       

      Subcat2

      5,550

      1,255

      11,100

       

      Cat 3

       

      17,942

      1,878

      31,080

       

       

      subcat3

      7,300

      -34

      8,880

       

       

      subcat4

      10,642

      1,912

      22,200

      Proj 1 Total

       

       

      22,675

      3,378

      54,000

      Proj 1

      Cat 1

       

      9,445

      1,850

      10,000

       

      Cat 2

       

      6,249

      1,185

      8,000

       

       

      Subcat1

      2,260

      296

      3,000

       

       

      Subcat2

      3,989

      889

      5,000

       

      Cat 3

       

      6,980

      342

      14,000

       

       

      subcat3

      1,944

      -202

      4,000

       

       

      subcat4

      5,036

      545

      10,000

      Proj 2 Total

       

       

      21,077

      1,931

      65,880

      Proj 2

      Cat 1

       

      6,435

      -534

      12,200

       

      Cat 2

       

      3,680

      929

      9,760

       

       

      Subcat1

      2,119

      564

      3,660

       

       

      Subcat2

      1,561

      366

      6,100

       

      Cat 3

       

      10,961

      1,536

      17,080

       

       

      subcat3

      5,355

      169

      4,880

       

       

      subcat4

      5,606

      1,367

      12,200

      Group 2

      Group 2 Total

       

       

      33,496

      3,305

      80,374

       

      Cat 1

       

      3,706

      219

      14,884

       

      Cat 2

       

      11,776

      952

      11,907

       

       

      Subcat1

      3,252

      310

      4,465

       

       

      Subcat2

      8,524

      642

      7,442

       

      Cat 3

       

      3,119

      592

      20,838

       

       

      subcat3

      3,119

      592

      5,954

       

       

      subcat4

      0

      0

      14,884

      Proj 3 Total

       

       

      33,496

      3,305

      80,374

      Proj 3

      Cat 1

       

      3,706

      219

      14,884

       

      Cat 2

       

      11,776

      952

      11,907

       

       

      Subcat1

      3,252

      310

      4,465

       

       

      Subcat2

      8,524

      642

      7,442

       

      Cat 3

       

      3,119

      592

      20,838

       

       

      subcat3

      3,119

      592

      5,954

       

       

      subcat4

      0

      0

      14,884