Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.