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

# How do multiple dimensions in an LOD expression work?

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?

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

{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?

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?