Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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...
Hello, Jean!
You can use a set analysis:
sum({<date={"<03/05/2017"},state=-{'CA'}>}value)
or the same expression for customers count.
May be like this
Dimension
Customer
Expression
Sum({<state -= {'CA'}, date = {"$(='<' & Date(MakeDate(2017, 5, 3), 'DD/MM/YYYY'))"}>} value)
Hi Jean,
Try the following expression in a calculated field:
=Aggr(MaxString({<customer=P({<state={*}-{CA},date={"<03/05/2017"}>})>} customer), customer)
Regards,
H
Or may be this
Sum({<Customer = p({<state -= {'CA'}, date = {"$(='<' & Date(MakeDate(2017, 5, 3), 'DD/MM/YYYY'))"}>} Customer)>} value)
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.