Percentage per month over any number of years

[#1]

Hello,

I've got the following table:

Month	2013	2014	2015	Amount
1	1000	1500	1800	4300
2	1100	1600	1830	4530
3	1200	1800	1840	4840
4	1050	1510	1850	4410
5	1070	1520	1860	4450
6	1250	1550	1800	4600
7	1300	1560	1800	4660
8	1200	1510	1900	4610
9	1100	2100	1820	5020
10	1030	1800	2500	5330
11	1000	1600	2100	4700
12	1100	1550	3000	5650
Sum	13400	19600	24100	57100

I want to calculate the percentage of every month over all years. As far as I can tell there are two ways to do this.

1.) I sum up all years per month and divide them by the total of all months.

So, the calculation for December would be 5650 / 57100 * 100 = 9.89%.

I figured out how to do this in QlikView.

It would look something like this:

sum({$ <Year = {2013, 2014, 2015}>} Amount) / sum(total {$ <Year = {2013, 2014, 2015}>} Amount)

2.) The second way is a little different, but this is the one I want to go with:

Calculate the percentage of each year, sum it all up an then divide the result by the number of years.

Again, for December:

1100 / 13400 * 100 = 8.21%

1550 / 19600 * 100 = 7.91%

3000 / 24100 * 100 = 12.45%

Sum up and divide:

(8.21 + 7.91 + 12.45) / 3 = 9.52%

Is there any way I can do that in QlikView without manually summing up every year?

Thank you!

[sunny_talwar]

Try this

Sum({$<Year = {2013, 2014, 2015}>} Amount)/Sum(TOTAL <Year> {$ <Year = {2013, 2014, 2015}>} Amount)

[Kushal_Chawda]

for second requirement

=avg(aggr(sum(Amount)/sum(total Amount),Year))

[sunny_talwar · Accepted solution]

That looks incomplete.... should it be this?

Avg(Aggr(Sum({$<Year = {2013, 2014, 2015}>} Amount)/Sum(TOTAL <Year> {$ <Year = {2013, 2014, 2015}>} Amount), Year, Month))

[#5]

Thank you. That worked!

[#6]

One more question:

Can I somehow make the selection of the year flexible? I've got a list box, and the User should be able to choose the years that are calculated.

I tried

Avg(Aggr(Sum({$<Year = {">=$(min(Year))<=$(max(Year))"}>} Amount)/Sum(TOTAL <Year> {$ <Year = {">=$(min(Year))<=$(max(Year))"}>} Amount), Year, Month))

but that doesn't seem to work. I think I know, why it doesn't work, but I can't think of any other way to do it right.

[sunny_talwar]

May be try just this

Avg(Aggr(Sum(Amount)/Sum(TOTAL <Year> Amount), Year, Month))