
1. Re: How to calculate the State of a store if I select the store with a parameter
Joshua Milligan Mar 23, 2014 4:16 PM (in response to Teresa Obis)Teresa,
There are probably multiple ways of solving this, but here's the path I took (by the way, I used the Superstore sample data, so instead of store I used customer; the logic will be the same).
First, I created a calculation to determine the state for the selected customer. Basically the same logic you had. Here's the code:
IF [Customer] == [Select a Customer] THEN [State] END
Then I created another calculation to filter out any customer who was not in the same state (please note that this assumes the customer always places orders from the same state  definitely not a safe assumption for a customer and in fact, this example gets wrong results  but it should work for a Store).
MIN(State) == WINDOW_MIN(MIN([Selected Customer's State]))
At this point, I have the selected Customer and all other customers in the same state. I can reuse the calculation from the first calculation above to separate the selected customers from all others if I desire. I simply used it as a row header, but hid the headers (right click the field and uncheck "Show Header").
Hopefully this helps get you started!
Regards,
Joshua

Calculated State.twbx 196.4 KB


2. Re: How to calculate the State of a store if I select the store with a parameter
Teresa Obis Mar 26, 2014 3:46 PM (in response to Joshua Milligan)Hi Joshua,
Thank you for your answer. As you said it helps me get started.
I try it and works, but it has two problems for me.
1) Early I have introduced the Store State as a constant e.g. [Constant introduced State]= "Maryland" and I've create several calculate fields (a lot) based in that variable. For example:
State Sales: IF State = [Constant introduced State] then Sales end
State Customers: IF State = [Constant introduced State] then Customer end
And then I perform other Table Calculation: Average Sales by Customer in State: SUM (State Sales) / COUNTD(Customer)
Now your calculation about if the customer is or not from the state:
MIN(State) == WINDOW_MIN(MIN([Selected Customer's State]))
is a table calculation so I have to change all my other calculated fields because I can not use Aggregate and No Aggegrate calculations at a time.
2) and the most important. I have a 2 Million row table, and this table calculation tooks around 5 seconds every time I would like to use it. Too much time.
Is there a way that I can extract the State of my Selected Customer and save it in all the rows as a calculate field (NOT a Table Calculation). That let me to use my actual calculated fields that runs instantly.
Teresa