
1. Re: Help with calculated field IF
Derrick Austin Mar 23, 2016 9:45 AM (in response to Samuel Gee)Hey Samuel,
I'm not quite sure I understand the end goal. Are you looking for a flag determining if the line item has entries in both 2015 and 2014?
If so, an LoD calculation is likely the solution you need.
Something like this:
{FIXED [Title] : IF [Label] = 'Label 1' AND Year=2014 THEN SUM([Sales]) END} > 0
AND {FIXED [Title] : IF [Label] = 'Label 2' AND Year=2015 THEN SUM([Sales]) END} > 0
Edit: Or rereading, maybe like this?
{FIXED [Title] : IF [Label] = 'Label 1' AND Year=2014 THEN SUM([Sales]) END} > 0 AND ZN({FIXED [Title] : IF [Label] = 'Label 2' AND Year=2015 THEN SUM([Sales]) END}) = 0
AND {FIXED [Title] : IF [Label] = 'Label 2' AND Year=2015 THEN SUM([Sales]) END} > 0 AND ZN({FIXED [Title] : IF [Label] = 'Label 1' AND Year=2014 THEN SUM([Sales]) END}) = 0

2. Re: Help with calculated field IF
Samuel Gee Mar 23, 2016 10:16 AM (in response to Derrick Austin)Thanks Derrick.
I only want titles that have sales in label 1 in 2014 and only have sales in label 2 in 2015. So those titles that transitioned from Label one to Label two basically as 2014 moved into 2015.
I think your formula does that? I'm getting errors on "Year = 2015" so I'm using DATEPART instead, but then I'm getting an error on mixing aggregate and nonaggregate comparisons.
{FIXED [Title] : IF [Label] = 'Label 1' AND DATEPART('year',[Year]) = 2014 THEN SUM([Sales]) END} > 0 AND ZN({FIXED [Title] : IF [Label] = 'Label 2' AND DATEPART('year',[Year]) = 2015 THEN SUM([Sales]) END}) = 0
AND {FIXED [Title] : IF [Label] = 'Label 2' AND DATEPART('year',[Year]) = 2015 THEN SUM([Sales]) END} > 0 AND ZN({FIXED [Title] : IF [Label] = 'Label 1' AND DATEPART('year',[Year]) = 2014 THEN SUM([Sales]) END}) = 0

3. Re: Help with calculated field IF
Derrick Austin Mar 23, 2016 10:18 AM (in response to Samuel Gee)Ah, that makes sense  the SUM should be outside the IF:
{FIXED [Title] : SUM(IF [Label] = 'Label 1' AND DATEPART('year',[Year]) = 2014 THEN [Sales] END)} > 0
AND ZN({FIXED [Title] : SUM(IF [Label] = 'Label 2' AND DATEPART('year',[Year]) = 2015 THEN [Sales] END)}) = 0
AND {FIXED [Title] : SUM(IF [Label] = 'Label 2' AND DATEPART('year',[Year]) = 2015 THEN [Sales] END)} > 0
AND ZN({FIXED [Title] : SUM(IF [Label] = 'Label 1' AND DATEPART('year',[Year]) = 2014 THEN [Sales] END)}) = 0