average of %'s

[YoussefBelloum]

Hi guys,

hope you're all doing great !

I'm struggling with this one below

please find the load inline

table:
LOAD *,'B' as grande_branche;
LOAD * Inline [
year, month, s, p
2016, 01, 23994, 1190025
2016, 02, 1783858, 44807
2016, 03, 3119771, 1101030
2016, 04, 2532433, 2257477
2016, 05, 1689517, 2239554
2016, 06, 1433957, 2240244
2016, 07, 1241190, 2267062
2016, 08, 1903950, 2295385
2016, 09, 2003044, 2446727
2016, 10, 1270384, 2314797
2016, 11, 650477, 2301929
2016, 12, 1016028, 920
2017, 01, 1468721, 3631043
2017, 02, 5184, 7099856
2017, 03, 1, 2312
2017, 04, 2379810, 1134792
2017, 05, 7561961, 8226690
2017, 06, 13914, 8142
2017, 07, 5035572, 26997
2017, 08, 2387585, 9967829
2017, 09, 2872092, -477
2017, 11, -5065559, 10147328
2017, 12, 2474849, 233
2018, 01, 1864423, 4010351
2018, 02, 1039546, 2591217
2018, 03, 2290030, 108
2018, 04, 2438133, 2651533
2018, 05, 2471542, 7503314
2018, 06, 37324, 148237
2018, 07, 4157580, 3758785
2018, 08, 2350730, 4967
2018, 09, 7116, 4334
2018, 10, 4261798, 8026595
2018, 11, 1782347, 12084
2019, 01, 2179030, 6818617
2019, 02, 2167150, 5417007
2019, 03, -1, -8581
2019, 04, 5387659, 6782298
2019, 05, 2481273, 5643248
2019, 06, 3003, 0
2019, 07, 4133789, 5774055
2019, 08, -4426963, 38142
2019, 09, 5002, 9249
2019, 10, 2050396, 8683003
2019, 11, 11790860, 2958232
2020, 01, 1063967, 7565504
2020, 02, 4805138, 15022
2020, 03, 1324, 699
2020, 04, 18337357, 9115237
2020, 05, 1000, 3059647
2020, 06, -3430421, 3809473
] (delimiter is ',');

here is the picture describing what i want to acheive

I need the average expression that gives me 90,6%...

I tried with avg(aggr(sum(s)/sum(p),year)) and other expressions but that is not working

Can you please take a look ?

Thank you

Youssef

[Kushal_Chawda]

I think % you are highlighting is different than what you get in the data you shared. So below formula should work for your need

avg(aggr(sum(s)/sum(p),year,grande_branche))

[YoussefBelloum]

Hi kush,

sorry, but the formula you gave me still gives me the wrong average "73%"...

[Kushal_Chawda · Accepted solution]

As per your data 73% seems to be correct

See the year wise breakup as per your data which is not matching with what you have highlighted. If I add all % I get 366. So 366/5 will give you 73%.

[YoussefBelloum]

My bad !

You're right, I made a mistake on my year filters values on my set analysis and I put 2020 for the different years...

Thank you, so the first expression was ok

[Kushal_Chawda]

No problem.