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: 
Anonymous
Not applicable

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])

5 Replies
oussema_laribi
Contributor II
Contributor II

like that ?

% total year.png

Anonymous
Not applicable
Author

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

oussema_laribi
Contributor II
Contributor II

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

this is the percentage  formula :

Sum (Total <year> sales).

Anonymous
Not applicable
Author

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?

dwforest
Specialist II
Specialist II

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

Check out:

Creating A Master Calendar