2 Replies Latest reply: Jun 6, 2017 4:32 PM by Jahanzeb Hashmi RSS

    Left Function

      How can I make this equation work?

       

      =sum({<Left(zip_property,3)=$(vPDZCheck),is_partner_property={1}>} total_referrals)

       

      I have an input box where people can type the first 3 numbers of a zip.  As you can see I have the variable as vPDZCheck for that input box and I am trying to get total_referrals by year for that 3 number zip that is a partner property.  Year_activity is my dimension.  When I take  {<Left(zip.....) out of the equation it works.  Can I have the "Left" function in a set part of the equation?  Thank you!

        • Re: Left Function
          Aaron Morgan

          Hi Kevin,

           

          The best option, if possible, would to create a new field in your load script that is Left(zip_property,3) as zip_property_3 (or another name) that would directly pick up your variable reference.

           

          You'll also need to wrap it in curly brackets, so =sum({<zip_property_3={$(vPDZCheck)}, is_partner_property={1}>}total_referrals)

           

          I tested that on a document and it seemed to work okay.

           

          You could also try using sum({<zip_property={'$(vPDZCheck)*'}... as this will pick up the first 3 characters and then use a wildcard to fill the rest of the field, which again seems to work okay, if you don't want to change your backend!

           

          I'm not quite sure about having a function on the field you're calculating values over in set analysis, but the syntax checker didn't have fun with it.

          • Re: Left Function
            Jahanzeb Hashmi

            Try this

            =sum({<aggr(zip_property,$(vPDZCheck)*),is_partner_property={1}>} total_referrals)