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

    Teresa Obis

      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,



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



          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!






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

            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.