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

    Help with calculated field IF

    Samuel Gee

      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
          Derrick Austin

          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

            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
              Derrick Austin

              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