Aggregation Issue

[#1]

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

[RedSky001]

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.

[#3]

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

[RedSky001]

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.