Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II
‎2019-01-24 10:20 AM

RangeSum with Average

Hi All,

Can someone please tell me if there is a way to calculate a rolling 5 year average using the RangeSum?  Here is what I have.

rangesum(Before(Count({<YR_SW = {1}>}DISTINCT([Sold To])),0,5))

Example of what I have 2013 has 1300 customers so the 2013's average would be 1300, then 2014 has 1200 customer and the rolling total would be 2500 and it's average would be 1250, etc.....

Thanks

Labels (1)
Labels
864 Views
0 Likes
1 Solution

Accepted Solutions
sunny_talwar
MVP
MVP
‎2019-01-24 10:25 AM

May be use RangeAvg() instead of RangeSum()

RangeAvg(Before(Count({<YR_SW = {1}>}DISTINCT([Sold To])),0,5))

View solution in original post

859 Views
0 Likes
4 Replies
tmumaw
Specialist II
Specialist II
‎2019-01-24 10:25 AM
Author

Ignore this post. I figured it out.
859 Views
0 Likes
sunny_talwar
MVP
MVP
‎2019-01-24 10:25 AM

May be use RangeAvg() instead of RangeSum()

RangeAvg(Before(Count({<YR_SW = {1}>}DISTINCT([Sold To])),0,5))
860 Views
0 Likes
tmumaw
Specialist II
Specialist II
‎2019-01-24 10:27 AM
Author

In response to sunny_talwar

Thanks Sunny I figured it out right after I hit the post button.

856 Views
tmumaw
Specialist II
Specialist II
‎2019-01-29 08:33 AM
Author

In response to sunny_talwar

Sunny,

Good morning.  I was wondering if there is a way to calculate a % of growth using the RangeSum?  What I am trying to do is find % of growth over a 11 year period by year.  I'm using this for the RangeAvg.  Thanks. The first column is actual and the second if from the statement below.

RangeAvg(Before(Count({<YR_SW = {1}>}DISTINCT([Sold To])),0,11))

Great Lakes Central1175311753 1069711225 1093511128 1141711201 1151311263 1144011293 1201411396 1211611486 1309911665 1336211835 1318411957 Great Lakes E1685916859 1608116470 1650816483 1668416533 1674916576 1713516669 1753616793 1773016910 1836417072 2001117366 1978117585 Great Lakes W1111711117 1060110859 1088110866 1126210965 1124011020 1168511131 1736012021 1813112785 1927213505 2039514194 2086914801 Mid Atlantic94169416 92139315 1289210507 1292611112 1250511390 1224211532 1264611691 1261111806 1283611921 1314212043 1306312136 N Chicago71257125 66786902 94217741 97628247 96268522 100038769 101758970 105739170 109339366 110039530 111859680 New England19551955 19511953 53403082 93164641 91635545 87826085 87376463 87046744 96357065 97687335 94937531 Northeast1495414954 1380814381 1368514149 1354213997 1323013844 1324413744 1369013736 1462613847 1491713966 1471014041 1468214099 S Chicago77527752 73687560 2063211917 2099114186 2052515454 2064516319 2100516988 2098817488 2150317934 2220518361 2260718747 South Central1837218372 1676217567 1620917114 1604916848 1678616836 1684416837 1759716946 1808617088 1914117316 1906117491 1990817710 Southeast1038810388 93939891 94569746 93949658 93589598 99899663 98609691 99259720 112089886 1344210241 1352310540 Western1232812328 1118311756 1116911560 1139211518 1111211437 1183211503 1198011571 1313311766 1322511928 1304012039 1272712102 
825 Views
0 Likes