Qlik Community

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
jaspreet_mehra2
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_TypeDistributionNeed This
A-92-6%
B-30-2%
C906%
D131%
E-111-7%
F49432%
G28618%
H40126%
I50032%

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.

10 Replies
Highlighted
Partner
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
jaspreet_mehra2
New Contributor

Re: Not able to calculate Average distribution

Hello,

Please find below sample raw data.

Sorry unable to PFA raw data

    

CONNECTION TYPEEmp IdAmount
E2801811230
E2803451230
E280725342-68.4
E280725342-68.4
E280725342-68.4
C1.60473E+32-1008
C1.60473E+32-1008
G1.60473E+32-1008
C1.87063E+32-1588
A1.51E+46100
C1.00206E+35-360
C1.00221E+182320
F1.00221E+182320
C1.00221E+182320
C1.00295E+35-1050
F1.00339E+350.06
C1.00388E+35-799
C1.004E+35-835.68
C1.004E+35-835.68
C1.004E+35-835.68
C1.004E+35-835.68
F1.00551E+350
A6.23E+46100
G1.00639E+350
F1.0064E+350
A8.63E+46100
G8.63E+46100
F8.63E+46100
UNKNOWN1.01022E+210
G1.01037E+311520
G1.01078E+35356.47
G1.01078E+31-66.62
F1.0108E+350
A1.09E+47100
F1.09E+47100
F1.09E+47100
A1.09E+47100
D1.01345E+210
D1.01345E+210
F1.01413E+350
D1.01652E+210
D1.01652E+210
D1.01652E+210
UNKNOWN1.01652E+210
D1.01652E+210
D1.01841E+210
D1.01842E+210
D1.01844E+210
D1.01846E+190
D1.01851E+210
UNKNOWN1.01851E+21-1324.69
UNKNOWN1.01859E+221872.83
UNKNOWN1.01859E+22832.27
D1.0186E+220
UNKNOWN1.0186E+22265.11
UNKNOWN1.0186E+22265.11
D1.0186E+220
UNKNOWN1.0186E+22915.03
D1.01862E+220
A010300268DN1345-134.1
A010400499DN1345-159
A010400499DS13410-160
A010400982DS1342-160
A010401468DN13450
A010544DS1342-32
A010700500DS13450
A010700504DS13440
A010700733DS3344-128
A010800725DS1344-51.2
A010800814DS13440
A010801811DS1342-128
A010801927DS1344-40
A010802300DS2343-128
F1.08801E+110
A011827DS1343-160
A011001923DS13470
A11040142345-42
A011200224DS1349-160
A011201065DN1343-119.2
A011201065DS13412-89.6
A020200839DN1342-119.2
A020250949DN1345-110
G20380476349-1313.82
A020400319DS1348-115
A020401548DS1346-99
B20470661345-120
A020501729DN1343-119.2
A020501729DS1344-119.2
A020601393DS11230
A020602323DN1342-350
A020702131DS1342-16
B210303123430
B306703383470
G407013803470
G407013813480
B50200663341-11.68
G504011613440
G50573348115
B506015341-50
H508029773420
G511026493430
G511026523430
B51202387344-199
H602032673430
H602049413490
H604034803430
B60503883343-110
H609029713410
B704023053410
B706033593440
B70702430343-540
H70800566343-1470
B711031433410
B100466344-140
B1882349-25
B1883342-25
B3556344-25
B3836344-315
B4760346-119
B2818342-25
B2865342-25
B2927344-25
B2928344-25
B2929346-110
B2938346-25
H90402220348-214
H9.04022E+11-95
H90901382342-110
H91101822341-1100
H1.00402E+110
H1.005E+110
H1.10303E+11-1851.19
H111138341-135
H1.20303E+11-526
H1.20602E+110
D2.06715E+160
D2.07846E+160
D2.08513E+190
D2.09413E+190
UNKNOWN2.00932E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00937E+220
UNKNOWN2.00939E+222276.52
UNKNOWN2.00939E+222276.52
H1818841800
H1818841800
H4201104760
F4201145550
B1149240
F1149240
B1205710
B1205710
G4201263652
G4201266149.92
H4201266149.92
C4201299649
C4201299649
E42013110
E42013110
E42013151750
E42013151750
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
jaspreet_mehra2
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 TYPEEmp IdAmount
E2801811230
E2803451230
E280725342-68.4
E280725342-68.4
E280725342-68.4
C1.60473E+32-1008
C1.60473E+32-1008
G1.60473E+32-1008
C1.87063E+32-1588
A1.51E+46100
C1.00206E+35-360
C1.00221E+182320
F1.00221E+182320
C1.00221E+182320
C1.00295E+35-1050
F1.00339E+350.06
C1.00388E+35-799
C1.004E+35-835.68
C1.004E+35-835.68
C1.004E+35-835.68
C1.004E+35-835.68
F1.00551E+350
A6.23E+46100
G1.00639E+350
F1.0064E+350
A8.63E+46100
G8.63E+46100
F8.63E+46100
UNKNOWN1.01022E+210
G1.01037E+311520
G1.01078E+35356.47
G1.01078E+31-66.62
F1.0108E+350
A1.09E+47100
F1.09E+47100
F1.09E+47100
A1.09E+47100
D1.01345E+210
D1.01345E+210
F1.01413E+350
D1.01652E+210
D1.01652E+210
D1.01652E+210
UNKNOWN1.01652E+210
D1.01652E+210
D1.01841E+210
D1.01842E+210
D1.01844E+210
D1.01846E+190
D1.01851E+210
UNKNOWN1.01851E+21-1324.69
UNKNOWN1.01859E+221872.83
UNKNOWN1.01859E+22832.27
D1.0186E+220
UNKNOWN1.0186E+22265.11
UNKNOWN1.0186E+22265.11
D1.0186E+220
UNKNOWN1.0186E+22915.03
D1.01862E+220
A010300268DN1345-134.1
A010400499DN1345-159
A010400499DS13410-160
A010400982DS1342-160
A010401468DN13450
A010544DS1342-32
A010700500DS13450
A010700504DS13440
A010700733DS3344-128
A010800725DS1344-51.2
A010800814DS13440
A010801811DS1342-128
A010801927DS1344-40
A010802300DS2343-128
F1.08801E+110
A011827DS1343-160
A011001923DS13470
A11040142345-42
A011200224DS1349-160
A011201065DN1343-119.2
A011201065DS13412-89.6
A020200839DN1342-119.2
A020250949DN1345-110
G20380476349-1313.82
A020400319DS1348-115
A020401548DS1346-99
B20470661345-120
A020501729DN1343-119.2
A020501729DS1344-119.2
A020601393DS11230
A020602323DN1342-350
A020702131DS1342-16
B210303123430
B306703383470
G407013803470
G407013813480
B50200663341-11.68
G504011613440
G50573348115
B506015341-50
H508029773420
G511026493430
G511026523430
B51202387344-199
H602032673430
H602049413490
H604034803430
B60503883343-110
H609029713410
B704023053410
B706033593440
B70702430343-540
H70800566343-1470
B711031433410
B100466344-140
B1882349-25
B1883342-25
B3556344-25
B3836344-315
B4760346-119
B2818342-25
B2865342-25
B2927344-25
B2928344-25
B2929346-110
B2938346-25
H90402220348-214
H9.04022E+11-95
H90901382342-110
H91101822341-1100
H1.00402E+110
H1.005E+110
H1.10303E+11-1851.19
H111138341-135
H1.20303E+11-526
H1.20602E+110
D2.06715E+160
D2.07846E+160
D2.08513E+190
D2.09413E+190
UNKNOWN2.00932E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00937E+220
UNKNOWN2.00939E+222276.52
UNKNOWN2.00939E+222276.52
H1818841800
H1818841800
H4201104760
F4201145550
B1149240
F1149240
B1205710
B1205710
G4201263652
G4201266149.92
H4201266149.92
C4201299649
C4201299649
E42013110
E42013110
E42013151750
E42013151750

Reply Actions

Jaspreet Mehra

Jaspreet Mehra (to Jordy Wegman) 4 hours ago

Hello,

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

   

CONNECTION TYPEEmp IdAmount
E2801811230
E2803451230
E280725342-68.4
E280725342-68.4
E280725342-68.4
C1.60473E+32-1008
C1.60473E+32-1008
G1.60473E+32-1008
C1.87063E+32-1588
A1.51E+46100
C1.00206E+35-360
C1.00221E+182320
F1.00221E+182320
C1.00221E+182320
C1.00295E+35-1050
F1.00339E+350.06
C1.00388E+35-799
C1.004E+35-835.68
C1.004E+35-835.68
C1.004E+35-835.68
C1.004E+35-835.68
F1.00551E+350
A6.23E+46100
G1.00639E+350
F1.0064E+350
A8.63E+46100
G8.63E+46100
F8.63E+46100
UNKNOWN1.01022E+210
G1.01037E+311520
G1.01078E+35356.47
G1.01078E+31-66.62
F1.0108E+350
A1.09E+47100
F1.09E+47100
F1.09E+47100
A1.09E+47100
D1.01345E+210
D1.01345E+210
F1.01413E+350
D1.01652E+210
D1.01652E+210
D1.01652E+210
UNKNOWN1.01652E+210
D1.01652E+210
D1.01841E+210
D1.01842E+210
D1.01844E+210
D1.01846E+190
D1.01851E+210
UNKNOWN1.01851E+21-1324.69
UNKNOWN1.01859E+221872.83
UNKNOWN1.01859E+22832.27
D1.0186E+220
UNKNOWN1.0186E+22265.11
UNKNOWN1.0186E+22265.11
D1.0186E+220
UNKNOWN1.0186E+22915.03
D1.01862E+220
A010300268DN1345-134.1
A010400499DN1345-159
A010400499DS13410-160
A010400982DS1342-160
A010401468DN13450
A010544DS1342-32
A010700500DS13450
A010700504DS13440
A010700733DS3344-128
A010800725DS1344-51.2
A010800814DS13440
A010801811DS1342-128
A010801927DS1344-40
A010802300DS2343-128
F1.08801E+110
A011827DS1343-160
A011001923DS13470
A11040142345-42
A011200224DS1349-160
A011201065DN1343-119.2
A011201065DS13412-89.6
A020200839DN1342-119.2
A020250949DN1345-110
G20380476349-1313.82
A020400319DS1348-115
A020401548DS1346-99
B20470661345-120
A020501729DN1343-119.2
A020501729DS1344-119.2
A020601393DS11230
A020602323DN1342-350
A020702131DS1342-16
B210303123430
B306703383470
G407013803470
G407013813480
B50200663341-11.68
G504011613440
G50573348115
B506015341-50
H508029773420
G511026493430
G511026523430
B51202387344-199
H602032673430
H602049413490
H604034803430
B60503883343-110
H609029713410
B704023053410
B706033593440
B70702430343-540
H70800566343-1470
B711031433410
B100466344-140
B1882349-25
B1883342-25
B3556344-25
B3836344-315
B4760346-119
B2818342-25
B2865342-25
B2927344-25
B2928344-25
B2929346-110
B2938346-25
H90402220348-214
H9.04022E+11-95
H90901382342-110
H91101822341-1100
H1.00402E+110
H1.005E+110
H1.10303E+11-1851.19
H111138341-135
H1.20303E+11-526
H1.20602E+110
D2.06715E+160
D2.07846E+160
D2.08513E+190
D2.09413E+190
UNKNOWN2.00932E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00934E+200
UNKNOWN2.00937E+220
UNKNOWN2.00939E+222276.52
UNKNOWN2.00939E+222276.52
H1818841800
H1818841800
H4201104760
F4201145550
B1149240
F1149240
B1205710
B1205710
G4201263652
G4201266149.92
H4201266149.92
C4201299649
C4201299649
E42013110
E42013110
E42013151750
E42013151750

Highlighted

Re: Not able to calculate Average distribution

Please share sample QVW instead copy paste data from community.

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
jaspreet_mehra2
New Contributor

Re: Not able to calculate Average distribution

Please refer the below link...

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.
jaspreet_mehra2
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 TYPEAverage Cost Per EmployeeNeed ThisNeeded % is
A-71-17%-71/424
B-39-9%-39/424
C-334-79%-334/424
D-11-3%-11/424
E33679%336/424
F5012%50/424
G9422%94/424
H-155-36%-155/424
UNKNOWN554130%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
rahulgoyal1287
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 TYPEAverage Cost Per EmployeeNeed ThisNeeded % is
A-71-17%-71/424
B-39-9%-39/424
C-334-79%-334/424
D-11-3%-11/424
E33679%336/424
F5012%50/424
G9422%94/424
H-155-36%-155/424
UNKNOWN554130%554/424


let me know if it works.


Rahul