5 Replies Latest reply: Mar 28, 2018 2:00 PM by David Forest

# Calculating Percentages in Table Based on Filters

New to Qlik and trying to create tables with percentages that are changed by the filters.

My excel spreadsheet has Quarter, Service, Zone, Company, 2016 counts, 2015 counts, 2014 counts, and 2013 counts.

I want to create a table similar to the following

Service           2013      2014              2015        2016

A

B

C

D

Etc.

The values in each of these would be a percentage of the total for that year.  I completed this by doing

sum [2016]/sum (TOTAL <Service> [2016]) which seemed to work until I tried to filter by Zone and Company.  This was placed as formula in each column with the year changed for each column.

Does anyone know a way to get the formula to accurately change by these filters?

I also tried sum[2016]/sum (TOTAL <Service, Zone, Quarter, Company> [2016]) but it didn't seem to work.

In another table I want to do the exact same thing but set the company to ours.  Again, I thought it was working until I tried filtering by zone and company

(Sum({<Company={"ABC"}>} [2016]))/Sum (TOTAL <Service, Zone, Quarter> [2016])

• ###### Re: Calculating Percentages in Table Based on Filters

like that ?

• ###### Re: Calculating Percentages in Table Based on Filters

I believe it would be similar to that as long as that is what you created in Qlik and not what the original file looked like.  Is that impacted by any other filters?

Thank you

• ###### Re: Calculating Percentages in Table Based on Filters

Yes it's created in Qlik and it's impacted by any other filtres ,

this is the percentage  formula :

Sum (Total <year> sales).

• ###### Re: Calculating Percentages in Table Based on Filters

I tried that but it doesn't seem to be working.  How does it calculate to be specific to each year? For your column 2014 percentage, there is more to your formula, right?

• ###### Re: Calculating Percentages in Table Based on Filters

It's assuming you have a field called Year and not displaying Year using Year(datefield).

Check out:

Creating A Master Calendar