3 Replies Latest reply: Jun 26, 2015 6:46 AM by jagan mohan rao appala RSS

    CITY Dimension wise TOP 5 Members(Dimension) Total

      Dear All,

      Below is the sample table and desire output what i am looking for. ( attached Excel and QVW for same)

      Ex.

      i have a sample table which have MEMBER, CITY AND TURNOVER.

      SAMPLE TABLE

      MEMBER CITY TURNOVER
      ADELHI200
      AMUMBAI100
      BDELHI100
      BMUMBAI300
      BPUNE500
      CDELHI50
      CMUMBAI250
      CPUNE600
      DDELHI25
      DMUMBAI50
      DPUNE200
      EDELHI225
      EMUMBAI25
      EPUNE125
      FDELHI300
      FMUMBAI110
      FPUNE150

       

      FIRST LEVEL OF OUTPUT: ( CITY wise TURNOVER of selected MEMBER)

      MEMBER SELECTED : 'B'

      OUTPUT:

      CITY Sum(TURNOVER)
      900
      MUMBAI300
      DELHI100
      PUNE500

       

      The above is very simple and easy. now i required a extra COLUMN Name "TOP 5 MEMBERS TURNOVER Like below:

      Here i have selected MEMBER 'B' so SUM(TURNOVER) getting CITY wise SUM of TURNOVER of MEMBER 'B'

       

      Now in the new COLUMN i want sum of TOP 5 MEMBERs of that CITY in the NEW Column.

       

       

      CITY Sum(TURNOVER) TOP 5 Members Turnover
      900
      MUMBAI300
      DELHI100
      PUNE500

       

      for example if i selected MEMBER 'B' the final output be like Below:

       

       

      CITY Sum(TURNOVER) TOP 5 Members Turnover
      900
      MUMBAI300810
      DELHI100875
      PUNE5001575

       

      810 in MUMBAI comes because top 5 Members TOTAL TURNOVER in MUMBAI City is 810. same go as for others city

      I am adding you the sample QVW and Excel file for ready reference.