Left Function

[#1]

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!

[morganaaron]

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.

[zebhashmi]

Try this

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