2 Replies Latest reply on Mar 26, 2014 3:46 PM by Teresa Obis

# How to calculate the State of a store if I select the store with a parameter

Hello my friends,

I have a parameter that let me to select a Store.

I would like to compare the sales of that Store with the sales of stores that are to the same state.

I wolud like to something like:

Sales Store = if store_name=parameter.store_name then sales end

Sales State = if store_state=VALUE OF STATE FOR THE (parameter.store_name) then sales end

It looks so easy, but I do not know how to calculate it.

Please, can you help me?

Thank you,

Teresa

• ###### 1. Re: How to calculate the State of a store if I select the store with a parameter

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

• ###### 2. Re: How to calculate the State of a store if I select the store with a parameter

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