Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Percent of Total Average

Thank you

9 Replies

Re: Percent of Total Average

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
Highlighted
rgreben
New Contributor III

Re: Percent of Total Average

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

Re: Percent of Total Average

ok
Khan_Mohammed
Honored Contributor II

Re: Percent of Total Average

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

Re: Percent of Total Average

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

Khan_Mohammed
Honored Contributor II

Re: Percent of Total Average

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

Re: Percent of Total Average

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?

Khan_Mohammed
Honored Contributor II

Re: Percent of Total Average

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

Re: Percent of Total Average

Thank you