1 Reply Latest reply on Mar 8, 2017 12:10 PM by Jennifer Spero

    Help with dynamic benchmarks

    Jennifer Spero

      Hi, I am using the below formula to show a benchmark based on the LOB selected from a filter. It works, however when you select more than one LOB ie/ home and mobile it does not work but home and brand does, and mobile and brand does. Can someone assist so that nothing shows if more than one LOB is selected?

       

      IF [Benchmark Toggle] AND ATTR([LOB])='Mobile' THEN

      ((SUM(CASE [LOB] WHEN 'Mobile' THEN [Clicks]ELSE NULL END))

      /

      (SUM(CASE [LOB]

          WHEN 'Mobile' THEN [Impressions]

          ELSE NULL END)))

       

       

      ELSEIF  [Benchmark Toggle] AND ATTR([LOB])='Home' THEN

      ((SUM(CASE [LOB] WHEN 'Home' THEN [Clicks]ELSE NULL END))

      /

      (SUM(CASE [LOB]

          WHEN 'Home' THEN [Impressions]

          ELSE NULL END)))

       

       

      ELSEIF [Benchmark Toggle] AND ATTR([LOB])='E-Commerce' THEN

      ((SUM(CASE [LOB] WHEN 'E-Commerce' THEN [Clicks]ELSE NULL END))

      /

      (SUM(CASE [LOB]

          WHEN 'E-Commerce' THEN [Impressions]

          ELSE NULL END)))

       

       

      ELSEIF [Benchmark Toggle] AND ATTR([LOB])='Brand/ Experiential/Customer Care' THEN

      ((SUM(CASE [LOB] WHEN 'Brand/ Experiential/Customer Care' THEN [Clicks]ELSE NULL END))

      /

      (SUM(CASE [LOB]

          WHEN 'Brand/ Experiential/Customer Care' THEN [Impressions]

          ELSE NULL END)))

       

       

      ELSE NULL

      END