E() or p() in if statement

[jduluc12]

Hi,

I have data set here

date                             customer                  value        state

01/02/2017                  cust1                         10            CA

02/03/2017                  cust2                         20             FL

03/04/2017                  cust3                         50             NY

04/04/2017                  cust4                         69             CA

05/04/2017                  cust4                         69             IL

I want to see if there is any customer who is not from state CA in our system before a certain date. lets say before 03/05/2017.

I can write a dimension like this

if (not match(state, 'CA') and date<'03/05/2017', customer)

it gives the data but i am just wondering if we can E() or P() in this if statement to bring out the same result.

-Jean

[Anonymous]

If you need your calculation row by row you can't use set analysis at all.

E() and P() are only avaible as a set modifier within set analysis. If you need just a preselection you could use something like that:

sum({<

      date={"<='03/05/17'"},state-={"CA"}

      >} value)

--> no P() or E() for this example...

[Sergey_Shuklin]

Hello, Jean!

You can use a set analysis:
sum({<date={"<03/05/2017"},state=-{'CA'}>}value)

or the same expression for customers count.

[sunny_talwar]

May be like this

Dimension

Customer

Expression

Sum({<state  -= {'CA'}, date = {"$(='<' & Date(MakeDate(2017, 5, 3), 'DD/MM/YYYY'))"}>} value)

[hector_munoz]

Hi Jean,

Try the following expression in a calculated field:

=Aggr(MaxString({<customer=P({<state={*}-{CA},date={"<03/05/2017"}>})>} customer), customer)

Regards,
H

[sunny_talwar]

Or may be this

Sum({<Customer = p({<state  -= {'CA'}, date = {"$(='<' & Date(MakeDate(2017, 5, 3), 'DD/MM/YYYY'))"}>} Customer)>} value)

[swuehl · Accepted solution]

This one used as dimension (e.g. in a list box) returns customer 2 and 3, with a date format set to 'DD/MM/YYYY'

=Aggr(Only({<customer = e({1<state = {'CA'}, date = {"<03/05/2017"}>} ) >} customer), customer)

Note that this is different from your original expression, though could be correct, if I understand your request correctly.