Announcements
cancel
Showing results for
Did you mean:
Creator

## E() or p() in if statement

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

1 Solution

Accepted Solutions
MVP

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.

6 Replies
Anonymous
Not applicable

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...

Specialist

Hello, Jean!

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

or the same expression for customers count.

MVP

May be like this

Dimension

Customer

Expression

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

Specialist

Hi Jean,

Try the following expression in a calculated field:

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

Regards,
H

MVP

Or may be this

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

MVP

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.

Community Browser