title: Spotfire Custom Expression summarizing over dates and unique identifiers
body: <p>I am trying to make a bar chart in Spotfire where the y-axis is displayed via a custom expression with the x-axis being a year. </p>
  
  <p>I am using a multiplier for each year and then summarizing. Basically, I have a large dataset and I am using a published model that says each year, only a certain percentage of these data are actually important. So I am trying to  make a bar chart with just that percentage of "important" data. When my data contains only unique records, this expression works:</p>
  
  <pre><code>Sum(Case  
  when (Year([Date*])=2011) and ([Source]="A") then  0.43 
  when (Year([Date*])=2012) and ([Source]="A") then  0.44 
  when (Year([Date*])=2013) and ([Source]="A") then  0.45 
  when (Year([Date*])=2014) and ([Source]="A") then  0.47 
  when (Year([Date*])=2015) and ([Source]="A") then  0.48
  else 0 
  end)
  </code></pre>
  
  <p>However, my data does contain some duplicate records. For instance, my data looks something like this:</p>
  
  <pre><code>ID    Source    Date*       TA
  1     A         1/2/2013   C
  1     A         1/2/2013   D
  2     A         3/5/2015   E
  3     A         11/15/2012 B
  3     A         11/15/2012 C
  4     B         2/15/2014  B
  </code></pre>
  
  <p>If I used the above working code, I would end up double counting for record IDs 1 and 3. Note I also have data with a <code>Source &lt;&gt; A</code>, but I do not want to include those in the chart.</p>
  
  <p>I have tried something like this, but it is just completely wrong: </p>
  
  <pre><code>Sum(Case  
  when (Year([Date*])=2011) and ([Source]="A") then UniqueCount([ID]) * 0.43 
  when (Year([Date*])=2012) and ([Source]="A") then UniqueCount([ID]) * 0.44 
  when (Year([Date*])=2013) and ([Source]="A") then UniqueCount([ID]) * 0.45 
  when (Year([Date*])=2014) and ([Source]="A") then UniqueCount([ID]) * 0.47 
  when (Year([Date*])=2015) and ([Source]="A") then UniqueCount([ID]) * 0.48
  else 0 
  end)
  </code></pre>
  
  <p>How do I sum over different percentages by year and unique identifier? Do I need to consider some kind of over statement after my <code>then</code>? I'm stuck and would appreciate any guidance.</p>