Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 |
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%')
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 |
Please share sample QVW instead copy paste data from community.
Please refer the below link...
what is your expected output w.r.t to data??
can explain the logic of calculating distribution %
Regards,
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
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
Hello,
Please find below sample raw data. Sorry I am unable to PFA file