3 Replies Latest reply: May 30, 2013 10:53 AM by Mark Sheraton RSS

    Aggregation Issue

    Deepak Kumar

      Hi All,

       

      I am Facing one Issue . The Issue is thatI when i Select the Region from the Dimension then the value of Sales Qty Range i.e 0-30 and when i select one value suppose delhi than the value of the that Region Changed . Please look into this issue.

       

       

      Any help would be appreciated.

       

      Thanks

      Regards,

      Deepak

        • Re: Aggregation Issue
          Mark Sheraton

          Create a new pivot table type of chart.

          Create an dimension as regionGroup (cyclic dimension)

          Create a second dimension, this time as a calculated dimension:

           

          =if(AGGR(SUM({<ITEMGROUPID={'FG'},CUSTOMERGROUP={'SALES'}>}SALES_QTY),CUSTACCOUNT, SALESREGION)<31,dual('>0 and < 30',1)

          ,if(AGGR(SUM({<ITEMGROUPID={'FG'},CUSTOMERGROUP={'SALES'}>}SALES_QTY),CUSTACCOUNT, SALESREGION)<61,dual('>31 and < 60',2)

          ,if(AGGR(SUM({<ITEMGROUPID={'FG'},CUSTOMERGROUP={'SALES'}>}SALES_QTY),CUSTACCOUNT, SALESREGION)<101,dual('>61 and < 100',3)

          ,if(AGGR(SUM({<ITEMGROUPID={'FG'},CUSTOMERGROUP={'SALES'}>}SALES_QTY),CUSTACCOUNT, SALESREGION)<151,dual('>101 and < 150',4)

          ,if(AGGR(SUM({<ITEMGROUPID={'FG'},CUSTOMERGROUP={'SALES'}>}SALES_QTY),CUSTACCOUNT, SALESREGION)<201,dual('>151 and < 200',5)

          ,if(AGGR(SUM({<ITEMGROUPID={'FG'},CUSTOMERGROUP={'SALES'}>}SALES_QTY),CUSTACCOUNT, SALESREGION)<301,dual('>201 and < 300',6)

          ,if(AGGR(SUM({<ITEMGROUPID={'FG'},CUSTOMERGROUP={'SALES'}>}SALES_QTY),CUSTACCOUNT, SALESREGION)>300,dual('>300',7)

          )))))))

           

           

          Now create an expression as:

          count(DISTINCT CUSTACCOUNT)

           

          Drag the caclulated dimension from the row into the coloum header with the expression below it.

          You may also want to change null characters - with zeros (on the presentation tab)

           

          This should work.

          Mark

           

          Message was edited by: Mark Sheraton (I changed the aggregated dimension expression to use dual() so you can sort this more easily.

            • Re: Aggregation Issue
              Deepak Kumar

              Hi Mark ,

               

               

              Thanks for Reply. It not changing  now but sum of the first column is not coming correct. Total is showing 935 but exact sum is 952.

               

              One more thing i want to add three dimension in Cyclic Group.So it is necessary to mention the SalesRegion or Statename etc in the Calculated Dimension.

               

               

               

              Regards

              Deepak

                • Re: Aggregation Issue
                  Mark Sheraton

                  Hi Deepak,

                   

                  In my first column ">0 and < 30" there are 886 unique customer account's.  I don't understand how you are getting 935.

                    

                   

                  Think of the aggregated dimension as a mini chart

                  So for each CUSTACCOUNT & SALESREGION it returns the sum of SALES_QTY where ITEMGROUPID= 'FG' and  CUSTOMERGROUP='SALES'

                   

                  ie for the grouping where SALESREGION = 'DL' there are 165 unique CUSTACCOUNT's

                  500722          DL          712.25

                  500723          DL          869.8

                  500724          DL          1302.9

                  500725          DL          384.25

                  500726          DL          390

                  500727          DL          1377.15

                  500728          DL          401

                  500729          DL          696.4

                  500730          DL          180

                  500731          DL          401.75

                  500732          DL          302.2

                  500734          DL          11.25

                  500735          DL          117

                  500736          DL          51.5

                  500737          DL          29

                  500739          DL          145

                  500740          DL          53.75

                  500741          DL          461.95

                  500742          DL          1125.75

                  500743          DL          151.25

                  500744          DL          17.5

                  500745          DL          292.35

                  500747          DL          177.5

                  500750          DL          837.75

                  500751          DL          218.75

                  500753          DL          381.3

                  500754          DL          171.25

                  500755          DL          1701.15

                  500756          DL          5

                  500757          DL          533.25

                  500758          DL          61.25

                  500759          DL          90.25

                  500760          DL          1129.85

                  500761          DL          154.5

                  500762          DL          604.6

                  500764          DL          5847.8

                  500766          DL          62.5

                  500767          DL          535.5

                  500768          DL          255

                  500769          DL          344.95

                  500770          DL          320.05

                  500771          DL          997.65

                  500772          DL          37.5

                  500775          DL          1258.6

                  500776          DL          283.65

                  500777          DL          61

                  500778          DL          18.5

                  500779          DL          860

                  500780          DL          1128.5

                  500781          DL          444.5

                  500782          DL          708.1

                  500783          DL          332.85

                  500784          DL          279.8

                  500785          DL          120.25

                  500786          DL          37.5

                  500787          DL          920.85

                  500788          DL          239.7

                  500789          DL          488.75

                  500790          DL          15

                  500791          DL          1006.25

                  500792          DL          258.2

                  500793          DL          85

                  500794          DL          622.45

                  500796          DL          175.5

                  500797          DL          203.75

                  500798          DL          380

                  500799          DL          1049.95

                  500800          DL          316.4

                  500801          DL          250.85

                  500802          DL          47.5

                  500804          DL          3661.8

                  500805          DL          358.5

                  500806          DL          66.8

                  500807          DL          104.5

                  500808          DL          566.65

                  500809          DL          17.5

                  500810          DL          10.5

                  500811          DL          2.5

                  500812          DL          303.95

                  500813          DL          36.5

                  500816          DL          51.5

                  500817          DL          210.35

                  501234          DL          972.35

                  501235          DL          574.7

                  501236          DL          7615.4

                  501237          DL          588.55

                  501238          DL          433.75

                  501240          DL          53.5

                  501241          DL          1690.4

                  501628          DL          91.5

                  501629          DL          525.5

                  501699          DL          8221.25

                  501790          DL          123.5

                  501843          DL          7780.05

                  501844          DL          532

                  501946          DL          30

                  501947          DL          21.25

                  502186          DL          254.25

                  502187          DL          730

                  502236          DL          209

                  502238          DL          673.05

                  502239          DL          452.5

                  502350          DL          1026.75

                  502476          DL          99

                  503007          DL          382.65

                  503068          DL          105

                  503094          DL          830.6

                  503113          DL          77.45

                  503114          DL          3049

                  503218          DL          111

                  503220          DL          964.4

                  503366          DL          117

                  503553          DL          2176.55

                  503666          DL          390

                  503667          DL          78

                  503707          DL          868.8

                  503708          DL          549.9

                  503742          DL          27.5

                  503839          DL          95

                  503840          DL          1317.45

                  503841          DL          769.7

                  503872          DL          156

                  503902          DL          117

                  503903          DL          696.15

                  503912          DL          82.5

                  504013          DL          250

                  504050          DL          234

                  504091          DL          39

                  504214          DL          78

                  504215          DL          250

                  504236          DL          226.45

                  504240          DL          20

                  504251          DL          282.95

                  504335          DL          78

                  504388          DL          27.5

                  504463          DL          1597.3

                  504485          DL          117

                  504490          DL          117

                  504615          DL          39

                  504777          DL          264

                  504781          DL          29

                  504912          DL          63.7

                  504940          DL          261.75

                  504995          DL          92.5

                  505101          DL          103

                  505102          DL          103

                  505123          DL          26.25

                  505124          DL          17.5

                  505125          DL          25

                  505259          DL          64

                  505299          DL          78

                  505300          DL          64

                  505301          DL          78

                  505310          DL          40

                  505362          DL          78

                  505383          DL          39

                  505384          DL          39

                  505402          DL          78

                  505403          DL          25

                  600002          DL          0.05

                  600003          DL          0.1

                  600006          DL          4.1

                  600007          DL          0.8

                  600010          DL          16.2

                  600018          DL          20.8

                   

                     

                  In the calculated dimension you're then saying, I only way to see data where the total was between ">0 and < 30"

                  This returns a subset of the above data:

                   

                   

                  500734          DL          11.25

                  500737          DL          29

                  500744          DL          17.5

                  500756          DL          5

                  500778          DL          18.5

                  500790          DL          15

                  500809          DL          17.5

                  500810          DL          10.5

                  500811          DL          2.5

                  501946          DL          30

                  501947          DL          21.25

                  503742          DL          27.5

                  504240          DL          20

                  504388          DL          27.5

                  504781          DL          29

                  505123          DL          26.25

                  505124          DL          17.5

                  505125          DL          25

                  505403          DL          25

                  600002          DL          0.05

                  600003          DL          0.1

                  600006          DL          4.1

                  600007          DL          0.8

                  600010          DL          16.2

                  600018          DL          20.8

                   

                  Whatever your cylic group is, it will only be working with this "bucket" of data.

                   

                  Therefore If your cylic group is SalesRegion and the first value is DL, the distrinct CUSTACCOUNT is 25.

                  If your cylic group is STATENAME and the first value is Delhi, the distrinct CUSTACCOUNT is 25.

                   

                  If your cylic group is COUNTRYNAME you will see the following output.

                   

                   

                  Central Delhi         0

                  East Delhi            10

                  New Delhi             1

                  North Delhi            9

                  South Delhi           8

                  West Delhi            7

                  Total                     25

                   

                  There are multuple COUNTRYNAME's in SalesRegion DL you see the breakdown, but the total is still 25.

                   

                  I hope this helps.