1 Reply Latest reply on Apr 6, 2016 3:53 AM by John Sobczak

    How to Create a Set by a formula?

    Derek Chen

      Hi, I'm doing an online advertising analysis, I want to create a set that contains the keywords have high ad costs and high clicks. By "high", I mean higher than Average 1.2 times.


      I think it should be created, by the condition field within creating sets. I tired my formula, but it doesn't work, my formula is:


      sum([costs])>average(total([costs]) * 1.2 and sum([clicks])>average(total(clicks]) *1.2 However, it gave me a error called "unknown function total called"


      Can someone help me to write a correct formula?



        • 1. Re: How to Create a Set by a formula?
          John Sobczak

          You cannot use table calcs (Total) in your set formulas.  In fact you couldn't even create that calc outside a set as you cannot aggregate Total.


          A way around this might be to create an LOD fixed calc that gives you the average at a row level, then use that calculated field in your set formula.    eg.  {fixed [dimension] : sum([costs])/count([ads])}

          1 of 1 people found this helpful