Aggregation Issue

Tue, 28 May 2013 10:43:46 GMT

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

RedSky001 — Tue, 28 May 2013 15:14:27 GMT

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

Thu, 30 May 2013 05:46:37 GMT

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

RedSky001 — Thu, 30 May 2013 14:53:44 GMT

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.

topic Aggregation Issue in QlikView

Aggregation Issue

Re: Aggregation Issue

Re: Aggregation Issue

Re: Aggregation Issue