3 Replies Latest reply on Apr 9, 2018 10:11 AM by Deepak Rai

    How do multiple dimensions in an LOD expression work?

    Darin Bellisario

      Hi all! I actually have two questions related to LOD expressions.

       

      For both, take an example case: suppose I have a data set of sales transactions, including the following dimensions:

      [custID]  - unique ID for each customer

      [city] - city of transaction; each customer may transact in multiple cities

      [state] - state of transaction; each customer may transact in multiple states

       

      Question 1: how do LOD expressions work with multiple dimensions?

       

      In our example, suppose that we are interested a particular metric: the sales by customer *and* by city, i.e. we want to roll up at the customer and state level for any further calculations

       

      That might look like: { INCLUDE: [custID],[city]: SUM([sales]) }

       

      What would the result be if we looked at, for example, the total average of that? Would it average the customer-city net combinations?

      i.e. AVG( { INCLUDE: [custID],[city]: SUM([sales]) } )

      would take the sales for each customer-city combination, and average them; is that right?

       

      What would the result be if we averaged at a *state* level?

      i.e. the same AVG( { INCLUDE: [custID],[city]: SUM([sales]) } ), but with the window rows set to [state] dimension?

      Would that take the sales for each customer-city combination for cities *within a state* and average them?

       

      Question 2: how would Standard deviations work with such LOD expressions?

       

      For example, if you look at:

      STDEV( { INCLUDE: [custID],[city]: SUM([sales]) } )

      Would that give you the standard deviation in customer-city sales combinations?

       

       

       

       

      Thanks for any insight you can share!

      -Darin

        • 1. Re: How do multiple dimensions in an LOD expression work?
          Deepak Rai

          You are aggregating the aggregate, So whatever you get at for each customer-city combination, it would be averaged

           

          Your Correct Syntax is

           

          {INCLUDE city:AVG(sales)} 

           

          you may use FIXED also. It is Evaluated Early and has few advantages. You Dont Need Customer ID as you are aggregating at Higher Level. Each City would have different average as result of this.

           

          {INCLUDE State:AVG(sales)}   This is for each State

           

          If you need to Have STDEV for Each City and Customer you will write:

           

          {INCLUDE City, Customer ID:STDEV(sales)}

           

          But For just different cities, you would do:

          {INCLUDE City:STDEV(sales)}

           

          Same for State

           

          Hope that Helps.

          Thanks

          Deepak

           

           

           

          • 2. Re: How do multiple dimensions in an LOD expression work?
            Darin Bellisario

            Thanks Deepak - sorry I should clarify, I actually *do* want to average at the Cust-city level first

             

            A better example to describe the problem might actually be instead of City, year,

            i.e. if I wanted an 'annual sales by customer' field, it would be:

            { INCLUDE [CustID], [Year]: AVG(Sales)}

             

            Is that right? Will that perform the following operation:

            1. Average Sales for each unique Customer-Year combination

            2. Perform whatever window operations are required

            ?

             

            Another question that I have to follow up on the first one is: does the order of dimensions matter in LOD expressions?

             

            Thanks all for any advice!

            -Darin

            • 3. Re: How do multiple dimensions in an LOD expression work?
              Deepak Rai

              You are CORRECT. It would Perform Average Sales for each unique Customer-Year combination and you can manipulate it later too in other calcs.  No, Order is Not an Issue as It would Perform LOD based upon whatever you have included. But it is good approach to keep the order the way you want results.

              Thanks

              Deepak

              1 of 1 people found this helpful