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

    Calculating Percentages in Table Based on Filters

    Julie Walter

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