2 Replies Latest reply: Apr 19, 2013 6:08 PM by Sophia Carmien RSS

    Using set analysis for all data within selected date range

      Hi,

       

      I'm fairly new to Qlikview and this is my first time posting.

       

      I have a table with a field called "Date", a field called"Category" (with the category name) and a field called "Cases for Category" (with the number of incidents related to that category on that date).  Here is a simplified version of my data:

       

      DateCategoryCases for Category
      1/1/2013Category A14
      1/1/2013Category B7
      1/2/2013Category A10
      1/2/2013Category B4
      1/3/2013Category A5
      1/3/2013Category B2
      1/4/2013Category A9
      1/4/2013Category B4

       

      I want to make a List Box where the field listed is "Category" and there is an expression next to it that represents what percent of incidents were related to that category for the seletected date range.  So for example If I select 1/1/2013, I would like it to display this:

       

      Category A    66
      Category B    33

       

      So far, I have an expression using set analysis to get a percent of the whole, however if I select specific dates, it only calculates the % of all the instances, not the instances for just those dates.  Here's the expression:

       

      Round(100 * Sum([Cases for Category])/(Sum(total{1 - $}[Cases for Category])+Sum(total{$}[Cases for Category])),0.01)

       

      If I select just 1/1/2013 this is what I am getting now:

       

      Category A    25
      Category B    13

       

      Can anyone help?