Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
YoussefBelloum
Champion
Champion

average of %'s

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

P2.png

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

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

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%.

Annotation 2020-08-04 152740.png

 

View solution in original post

5 Replies
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
Champion
Champion
Author

Hi kush,

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

Kushal_Chawda

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%.

Annotation 2020-08-04 152740.png

 

YoussefBelloum
Champion
Champion
Author

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.