Re: Not able to calculate Average distribution - Qlik Community

Anonymous · ‎2018-10-24

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)

Thanks in advance.

JordyWegman · ‎2018-10-24

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

Anonymous · ‎2018-10-25

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

Anil_Babu_Samineni · ‎2018-10-25

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%')

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2018-10-25

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

Reply • Like Show 0 Likes(0)Actions

Jaspreet Mehra (to Jordy Wegman) 4 hours ago

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

Anil_Babu_Samineni · ‎2018-10-25

Please share sample QVW instead copy paste data from community.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2018-10-27

Please refer the below link...

Not able to calculate Average distribution

PrashantSangle · ‎2018-10-27

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Anonymous · ‎2018-10-27

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

rahulgoyal1287 · ‎2018-10-27

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