3 Replies Latest reply on Mar 23, 2016 10:18 AM by Derrick Austin

# Help with calculated field IF

Hey geniuses,

I have the below data:

http://i.imgur.com/3EQVMzb.png

I want to isolate any title that has sales in 2014 with label 1 and sales in 2015 with label 2 only. (So (Label 1 2014 sales > 0 and 2015 sales = 0) and (label 2 2014 sales = 0 and 2015 sales >0))

I'm having trouble with the calculated field though. Any help appreciated.

Sam

• ###### 1. Re: Help with calculated field IF

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

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 non-aggregate 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

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