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,

       

      Teresa

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

          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

           

          1.png

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

             

            Teresa