How to count the number of times an object starts by "13"

[#1]

Hello everybody,

I´m trying to figure out the best way to do the following:

I have an object that is "WorkerRegistrationNumber". This object always start by the two last digits of the year when it was created. If it was created in 2011, the registration number will start by "11". If it was created in 2014, it would start by "14".

What I want to do is the number of times I have an object starting by "13", for example. At the same time I also want to know how many of these objects (only those started by "13") are from a specific city (City = "New York").

Could you help me?

Best regards,

Bruno Ricardo

[maxgro]

count({$ <WorkerRegistrationNumber={"13*"}>}  somefield)

count({$ <WorkerRegistrationNumber={"13*"},City={"New York"}>}  somefield)

[MarcoWedel]

-Sum(WorkerRegistrationNumber like '13*')

[MarcoWedel]

-Sum(WorkerRegistrationNumber like '13*' and City='New York')

[simenkg]

In your script, create a field:

Load *,

     left(WorkerRegistrationNumber, 2) as WorkRegistrationYear

from....;

then you create a pivot table with the dimensions, WorkRegistrationYear and City and use the expression Count(WorkerRegistrationNumber) or Count(Distinct WorkerRegistrationNumber) depending on your datamodel.