# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
New Contributor

## Not able to calculate Average distribution

Hello Community,

I am try to show the distribution of Connections. Below is the example and I need percentage of distribution.

 Connection_Type Distribution Need This A -92 -6% B -30 -2% C 90 6% D 13 1% E -111 -7% F 494 32% G 286 18% H 401 26% I 500 32%

To calculate the distribution. I am using below set analysis that i can't change.

sum(aggr(SUM(DISTINCT Amount),Emp_id))/ COUNT(DISTINCT Emp_id)

10 Replies
Highlighted
Partner

## Re: Not able to calculate Average distribution

Hi Jaspreet,

Can you give us the values for the Amount and Emp_id?

Your current formula doesn't contain any set analysis btw.

Regards,

Jordy

Work smarter, not harder
Highlighted
New Contributor

## Re: Not able to calculate Average distribution

Hello,

Please find below sample raw data.

Sorry unable to PFA raw data

 CONNECTION TYPE Emp Id Amount E 280181123 0 E 280345123 0 E 280725342 -68.4 E 280725342 -68.4 E 280725342 -68.4 C 1.60473E+32 -1008 C 1.60473E+32 -1008 G 1.60473E+32 -1008 C 1.87063E+32 -1588 A 1.51E+46 100 C 1.00206E+35 -360 C 1.00221E+18 2320 F 1.00221E+18 2320 C 1.00221E+18 2320 C 1.00295E+35 -1050 F 1.00339E+35 0.06 C 1.00388E+35 -799 C 1.004E+35 -835.68 C 1.004E+35 -835.68 C 1.004E+35 -835.68 C 1.004E+35 -835.68 F 1.00551E+35 0 A 6.23E+46 100 G 1.00639E+35 0 F 1.0064E+35 0 A 8.63E+46 100 G 8.63E+46 100 F 8.63E+46 100 UNKNOWN 1.01022E+21 0 G 1.01037E+31 1520 G 1.01078E+35 356.47 G 1.01078E+31 -66.62 F 1.0108E+35 0 A 1.09E+47 100 F 1.09E+47 100 F 1.09E+47 100 A 1.09E+47 100 D 1.01345E+21 0 D 1.01345E+21 0 F 1.01413E+35 0 D 1.01652E+21 0 D 1.01652E+21 0 D 1.01652E+21 0 UNKNOWN 1.01652E+21 0 D 1.01652E+21 0 D 1.01841E+21 0 D 1.01842E+21 0 D 1.01844E+21 0 D 1.01846E+19 0 D 1.01851E+21 0 UNKNOWN 1.01851E+21 -1324.69 UNKNOWN 1.01859E+22 1872.83 UNKNOWN 1.01859E+22 832.27 D 1.0186E+22 0 UNKNOWN 1.0186E+22 265.11 UNKNOWN 1.0186E+22 265.11 D 1.0186E+22 0 UNKNOWN 1.0186E+22 915.03 D 1.01862E+22 0 A 010300268DN1345 -134.1 A 010400499DN1345 -159 A 010400499DS13410 -160 A 010400982DS1342 -160 A 010401468DN1345 0 A 010544DS1342 -32 A 010700500DS1345 0 A 010700504DS1344 0 A 010700733DS3344 -128 A 010800725DS1344 -51.2 A 010800814DS1344 0 A 010801811DS1342 -128 A 010801927DS1344 -40 A 010802300DS2343 -128 F 1.08801E+11 0 A 011827DS1343 -160 A 011001923DS1347 0 A 11040142345 -42 A 011200224DS1349 -160 A 011201065DN1343 -119.2 A 011201065DS13412 -89.6 A 020200839DN1342 -119.2 A 020250949DN1345 -110 G 20380476349 -1313.82 A 020400319DS1348 -115 A 020401548DS1346 -99 B 20470661345 -120 A 020501729DN1343 -119.2 A 020501729DS1344 -119.2 A 020601393DS1123 0 A 020602323DN1342 -350 A 020702131DS1342 -16 B 21030312343 0 B 30670338347 0 G 40701380347 0 G 40701381348 0 B 50200663341 -11.68 G 50401161344 0 G 50573348 115 B 506015341 -50 H 50802977342 0 G 51102649343 0 G 51102652343 0 B 51202387344 -199 H 60203267343 0 H 60204941349 0 H 60403480343 0 B 60503883343 -110 H 60902971341 0 B 70402305341 0 B 70603359344 0 B 70702430343 -540 H 70800566343 -1470 B 71103143341 0 B 100466344 -140 B 1882349 -25 B 1883342 -25 B 3556344 -25 B 3836344 -315 B 4760346 -119 B 2818342 -25 B 2865342 -25 B 2927344 -25 B 2928344 -25 B 2929346 -110 B 2938346 -25 H 90402220348 -214 H 9.04022E+11 -95 H 90901382342 -110 H 91101822341 -1100 H 1.00402E+11 0 H 1.005E+11 0 H 1.10303E+11 -1851.19 H 111138341 -135 H 1.20303E+11 -526 H 1.20602E+11 0 D 2.06715E+16 0 D 2.07846E+16 0 D 2.08513E+19 0 D 2.09413E+19 0 UNKNOWN 2.00932E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00937E+22 0 UNKNOWN 2.00939E+22 2276.52 UNKNOWN 2.00939E+22 2276.52 H 181884 1800 H 181884 1800 H 4201104 760 F 4201145 550 B 1149 240 F 1149 240 B 1205 710 B 1205 710 G 4201263 652 G 4201266 149.92 H 4201266 149.92 C 4201299 649 C 4201299 649 E 4201311 0 E 4201311 0 E 4201315 1750 E 4201315 1750
Highlighted

## Re: Not able to calculate Average distribution

Try this?

Num((Sum(aggr(SUM(DISTINCT Amount),Emp_id))/ COUNT(DISTINCT Emp_id)) /(sum(aggr(SUM(TOTAL Amount),Emp_id))/ COUNT(TOTAL Emp_id)) ,'#,##0%')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor

## Re: Not able to calculate Average distribution

Sorry Anil, its not giving want i am looking for. its giving me sum of individual connection type inside of sum of overall.

Please find below sample raw data for testing

 CONNECTION TYPE Emp Id Amount E 280181123 0 E 280345123 0 E 280725342 -68.4 E 280725342 -68.4 E 280725342 -68.4 C 1.60473E+32 -1008 C 1.60473E+32 -1008 G 1.60473E+32 -1008 C 1.87063E+32 -1588 A 1.51E+46 100 C 1.00206E+35 -360 C 1.00221E+18 2320 F 1.00221E+18 2320 C 1.00221E+18 2320 C 1.00295E+35 -1050 F 1.00339E+35 0.06 C 1.00388E+35 -799 C 1.004E+35 -835.68 C 1.004E+35 -835.68 C 1.004E+35 -835.68 C 1.004E+35 -835.68 F 1.00551E+35 0 A 6.23E+46 100 G 1.00639E+35 0 F 1.0064E+35 0 A 8.63E+46 100 G 8.63E+46 100 F 8.63E+46 100 UNKNOWN 1.01022E+21 0 G 1.01037E+31 1520 G 1.01078E+35 356.47 G 1.01078E+31 -66.62 F 1.0108E+35 0 A 1.09E+47 100 F 1.09E+47 100 F 1.09E+47 100 A 1.09E+47 100 D 1.01345E+21 0 D 1.01345E+21 0 F 1.01413E+35 0 D 1.01652E+21 0 D 1.01652E+21 0 D 1.01652E+21 0 UNKNOWN 1.01652E+21 0 D 1.01652E+21 0 D 1.01841E+21 0 D 1.01842E+21 0 D 1.01844E+21 0 D 1.01846E+19 0 D 1.01851E+21 0 UNKNOWN 1.01851E+21 -1324.69 UNKNOWN 1.01859E+22 1872.83 UNKNOWN 1.01859E+22 832.27 D 1.0186E+22 0 UNKNOWN 1.0186E+22 265.11 UNKNOWN 1.0186E+22 265.11 D 1.0186E+22 0 UNKNOWN 1.0186E+22 915.03 D 1.01862E+22 0 A 010300268DN1345 -134.1 A 010400499DN1345 -159 A 010400499DS13410 -160 A 010400982DS1342 -160 A 010401468DN1345 0 A 010544DS1342 -32 A 010700500DS1345 0 A 010700504DS1344 0 A 010700733DS3344 -128 A 010800725DS1344 -51.2 A 010800814DS1344 0 A 010801811DS1342 -128 A 010801927DS1344 -40 A 010802300DS2343 -128 F 1.08801E+11 0 A 011827DS1343 -160 A 011001923DS1347 0 A 11040142345 -42 A 011200224DS1349 -160 A 011201065DN1343 -119.2 A 011201065DS13412 -89.6 A 020200839DN1342 -119.2 A 020250949DN1345 -110 G 20380476349 -1313.82 A 020400319DS1348 -115 A 020401548DS1346 -99 B 20470661345 -120 A 020501729DN1343 -119.2 A 020501729DS1344 -119.2 A 020601393DS1123 0 A 020602323DN1342 -350 A 020702131DS1342 -16 B 21030312343 0 B 30670338347 0 G 40701380347 0 G 40701381348 0 B 50200663341 -11.68 G 50401161344 0 G 50573348 115 B 506015341 -50 H 50802977342 0 G 51102649343 0 G 51102652343 0 B 51202387344 -199 H 60203267343 0 H 60204941349 0 H 60403480343 0 B 60503883343 -110 H 60902971341 0 B 70402305341 0 B 70603359344 0 B 70702430343 -540 H 70800566343 -1470 B 71103143341 0 B 100466344 -140 B 1882349 -25 B 1883342 -25 B 3556344 -25 B 3836344 -315 B 4760346 -119 B 2818342 -25 B 2865342 -25 B 2927344 -25 B 2928344 -25 B 2929346 -110 B 2938346 -25 H 90402220348 -214 H 9.04022E+11 -95 H 90901382342 -110 H 91101822341 -1100 H 1.00402E+11 0 H 1.005E+11 0 H 1.10303E+11 -1851.19 H 111138341 -135 H 1.20303E+11 -526 H 1.20602E+11 0 D 2.06715E+16 0 D 2.07846E+16 0 D 2.08513E+19 0 D 2.09413E+19 0 UNKNOWN 2.00932E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00937E+22 0 UNKNOWN 2.00939E+22 2276.52 UNKNOWN 2.00939E+22 2276.52 H 181884 1800 H 181884 1800 H 4201104 760 F 4201145 550 B 1149 240 F 1149 240 B 1205 710 B 1205 710 G 4201263 652 G 4201266 149.92 H 4201266 149.92 C 4201299 649 C 4201299 649 E 4201311 0 E 4201311 0 E 4201315 1750 E 4201315 1750

Jaspreet Mehra (to Jordy Wegman)

Hello,

Please find below sample raw data. Sorry I am unable to PFA file

 CONNECTION TYPE Emp Id Amount E 280181123 0 E 280345123 0 E 280725342 -68.4 E 280725342 -68.4 E 280725342 -68.4 C 1.60473E+32 -1008 C 1.60473E+32 -1008 G 1.60473E+32 -1008 C 1.87063E+32 -1588 A 1.51E+46 100 C 1.00206E+35 -360 C 1.00221E+18 2320 F 1.00221E+18 2320 C 1.00221E+18 2320 C 1.00295E+35 -1050 F 1.00339E+35 0.06 C 1.00388E+35 -799 C 1.004E+35 -835.68 C 1.004E+35 -835.68 C 1.004E+35 -835.68 C 1.004E+35 -835.68 F 1.00551E+35 0 A 6.23E+46 100 G 1.00639E+35 0 F 1.0064E+35 0 A 8.63E+46 100 G 8.63E+46 100 F 8.63E+46 100 UNKNOWN 1.01022E+21 0 G 1.01037E+31 1520 G 1.01078E+35 356.47 G 1.01078E+31 -66.62 F 1.0108E+35 0 A 1.09E+47 100 F 1.09E+47 100 F 1.09E+47 100 A 1.09E+47 100 D 1.01345E+21 0 D 1.01345E+21 0 F 1.01413E+35 0 D 1.01652E+21 0 D 1.01652E+21 0 D 1.01652E+21 0 UNKNOWN 1.01652E+21 0 D 1.01652E+21 0 D 1.01841E+21 0 D 1.01842E+21 0 D 1.01844E+21 0 D 1.01846E+19 0 D 1.01851E+21 0 UNKNOWN 1.01851E+21 -1324.69 UNKNOWN 1.01859E+22 1872.83 UNKNOWN 1.01859E+22 832.27 D 1.0186E+22 0 UNKNOWN 1.0186E+22 265.11 UNKNOWN 1.0186E+22 265.11 D 1.0186E+22 0 UNKNOWN 1.0186E+22 915.03 D 1.01862E+22 0 A 010300268DN1345 -134.1 A 010400499DN1345 -159 A 010400499DS13410 -160 A 010400982DS1342 -160 A 010401468DN1345 0 A 010544DS1342 -32 A 010700500DS1345 0 A 010700504DS1344 0 A 010700733DS3344 -128 A 010800725DS1344 -51.2 A 010800814DS1344 0 A 010801811DS1342 -128 A 010801927DS1344 -40 A 010802300DS2343 -128 F 1.08801E+11 0 A 011827DS1343 -160 A 011001923DS1347 0 A 11040142345 -42 A 011200224DS1349 -160 A 011201065DN1343 -119.2 A 011201065DS13412 -89.6 A 020200839DN1342 -119.2 A 020250949DN1345 -110 G 20380476349 -1313.82 A 020400319DS1348 -115 A 020401548DS1346 -99 B 20470661345 -120 A 020501729DN1343 -119.2 A 020501729DS1344 -119.2 A 020601393DS1123 0 A 020602323DN1342 -350 A 020702131DS1342 -16 B 21030312343 0 B 30670338347 0 G 40701380347 0 G 40701381348 0 B 50200663341 -11.68 G 50401161344 0 G 50573348 115 B 506015341 -50 H 50802977342 0 G 51102649343 0 G 51102652343 0 B 51202387344 -199 H 60203267343 0 H 60204941349 0 H 60403480343 0 B 60503883343 -110 H 60902971341 0 B 70402305341 0 B 70603359344 0 B 70702430343 -540 H 70800566343 -1470 B 71103143341 0 B 100466344 -140 B 1882349 -25 B 1883342 -25 B 3556344 -25 B 3836344 -315 B 4760346 -119 B 2818342 -25 B 2865342 -25 B 2927344 -25 B 2928344 -25 B 2929346 -110 B 2938346 -25 H 90402220348 -214 H 9.04022E+11 -95 H 90901382342 -110 H 91101822341 -1100 H 1.00402E+11 0 H 1.005E+11 0 H 1.10303E+11 -1851.19 H 111138341 -135 H 1.20303E+11 -526 H 1.20602E+11 0 D 2.06715E+16 0 D 2.07846E+16 0 D 2.08513E+19 0 D 2.09413E+19 0 UNKNOWN 2.00932E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00934E+20 0 UNKNOWN 2.00937E+22 0 UNKNOWN 2.00939E+22 2276.52 UNKNOWN 2.00939E+22 2276.52 H 181884 1800 H 181884 1800 H 4201104 760 F 4201145 550 B 1149 240 F 1149 240 B 1205 710 B 1205 710 G 4201263 652 G 4201266 149.92 H 4201266 149.92 C 4201299 649 C 4201299 649 E 4201311 0 E 4201311 0 E 4201315 1750 E 4201315 1750

Highlighted

## Re: Not able to calculate Average distribution

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor

## Re: Not able to calculate Average distribution

Not able to calculate Average distribution

Highlighted

## Re: Not able to calculate Average distribution

what is your expected output w.r.t to data??

can explain the logic of calculating distribution %

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
New Contributor

## Re: Not able to calculate Average distribution

Hello,

I am try to show the distribution of Connections Type. Below is the table and i am calculating "Average Cost of Employee" by using below logic

Logic = sum(aggr(SUM(DISTINCT Amount),Emp_id))/ COUNT(DISTINCT Emp_id)

 CONNECTION TYPE Average Cost Per Employee Need This Needed % is A -71 -17% -71/424 B -39 -9% -39/424 C -334 -79% -334/424 D -11 -3% -11/424 E 336 79% 336/424 F 50 12% 50/424 G 94 22% 94/424 H -155 -36% -155/424 UNKNOWN 554 130% 554/424

I need third column which is distribution of "Average Cost of Employee"/ "Grant Total of Average Cost of Employee".

i.e A connection Type is -71/424 = -17%.

Fourth column is input of third column.

Please refer raw data from below path.Sorry i am unable to attached it

Not able to calculate Average distribution

Highlighted
New Contributor III

## Re: Not able to calculate Average distribution

Hello Jaspreet,

it looks simple.. you can use following formula to get the numbers -

SUM([Average Cost Per Employee]) / SUM( <TOTAL  NECTION TYPE> [Average Cost Per Employee])

 NECTION TYPE Average Cost Per Employee Need This Needed % is A -71 -17% -71/424 B -39 -9% -39/424 C -334 -79% -334/424 D -11 -3% -11/424 E 336 79% 336/424 F 50 12% 50/424 G 94 22% 94/424 H -155 -36% -155/424 UNKNOWN 554 130% 554/424

let me know if it works.

Rahul