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: 
rgreben
Contributor III
Contributor III

Percent of Total Average

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.

Percent of Total Average.png

 

1 Solution

Accepted Solutions
rgreben
Contributor III
Contributor III
Author

9 Replies
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
Contributor III
Contributor III
Author

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

sunny_talwar

ok
MK9885
Master II
Master II

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
Contributor III
Contributor III
Author

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

MK9885
Master II
Master II

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
Contributor III
Contributor III
Author

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
Master II
Master II

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
Contributor III
Contributor III
Author

Thank you