Percent of Total Average

[rgreben]

Hello Group! I would like to show prior 3 year average percent of total as highlighted in red. Prior 3 year average is based on years 2015 - 2017. The weeks shown are for fiscal November. I used =sum(NetSales)/Sum(Total <[Fiscal Year]> NetSales) to calculate percent of total but can't figure out how to calculate the averages. Please help. Thank you.

[sunny_talwar]

Would you be able to share a sample of where you are trying to do this? It would be easier to see what you have and help you build forward

[rgreben]

Sorry. I am working in QlikSense in a Pivot Table.

[sunny_talwar]

ok

[MK9885]

This would be previous 3 yr expression but you looking for a dimension

Sum({<[Fiscal Year] = {$(=Max([Fiscal Year])-1)} ,Month  >+< Year = {$(=Max([Fiscal Year])-2)} ,[Month Full Name]>+< [Fiscal Year] = {$(=Max([Fiscal Year])-3)} ,Month   >}NetSales)

/

Sum({<[Fiscal Year] = {$(=Max([Fiscal Year])-1)} ,Month  >+< [Fiscal Year] = {$(=Max([Fiscal Year])-2)} ,[Month Full Name]>+< [Fiscal Year] = {$(=Max([Fiscal Year])-3)} ,Month   >} NetSales)

Not sure how you would add an extra row in between generic year column

[rgreben]

Khan_Mohammed, thank you for your response. This formula does not seem to be working. I am getting 100% all the way through.

[MK9885]

Sorry forgot to add TOTAL in 2nd expression

Sum({<[Fiscal Year] = {$(=Max([Fiscal Year])-1)} ,Month >+< Year = {$(=Max([Fiscal Year])-2)} ,[Month Full Name]>+< [Fiscal Year] = {$(=Max([Fiscal Year])-3)} ,Month >}NetSales)

/

Sum({<[Fiscal Year] = {$(=Max([Fiscal Year])-1)} ,Month >+< [Fiscal Year] = {$(=Max([Fiscal Year])-2)} ,[Month Full Name]>+< [Fiscal Year] = {$(=Max([Fiscal Year])-3)} ,Month >} TOTAL NetSales)

[rgreben]

Khan-Mohammed, thank you. This formula gives me percent of total for each year but not the rolling 3 year average. What formula would I use to come up with 3 year rolling average as highlighted in my example?

[MK9885]

Thats correct.
As per your image you are looking to add a new value to your Fiscal year dimension.
Not sure how to achieve that but would let you know if I find a solution.
Thanks.

[rgreben · Accepted solution]

Thank you