Formula in Calculated Dimension

[#1]

Dear Experts,

I am using the below formula in the calculated dimension of a pivot table and getting "// Error in calculated dimension"

"if(sum(Stock_Qty)>0,If((sum(Stock_Qty)/AvrgSales)>$(VMonths),sum(Stock_Qty) - ($(VMonths) * AvrgSales),))"

Any help

[alexandros17]

You cannot use a sum without aggr condition in a dimension because a sum or a min, max ... function must be executed according to a dimension, a set of fields.

if you use Aggr(Sum(...), myField1, myFieldn) is like summing with  myField1, ... myFieldn as dimension

Hope it helps

[#3]

It helps but can you please explain more about the aggregate function and its use?

[alexandros17]

Aggr works like a group by in a sql query, so your sum(...) must be grouped by something if used as dimension.

... But why using it as a dimension, wouldn't be better to use as an expression computed according time, customer, city or something else?

[#5]

I have a table containing item and expiry date as dimensions.

I want to compute the overstock by item.

If I calculate in the expression it will calculate according to the exp date which I don't want.

So I want to use it as dimension to have itemName as dimension 1 OverStock(the calculated dimension) as dimension 2 then exp date as dimension 3.

Using "if(sum(Stock_Qty)>0,If((sum(Stock_Qty)/AvrgSales)>$(VMonths),sum(Stock_Qty) - ($(VMonths) * AvrgSales),))" I want to do the below:

1. if Stock_Qty >0
2. if (sum(Stock_Qty)/AvrgSales)>$(VMonths)
3. if both conditions are satisfied I want to calculate :sum(Stock_Qty) - ($(VMonths) * AvrgSales) by itemName

I tried "

=if(sum(Stock_Qty)>0,

If((sum(Stock_Qty)/AvrgSales)>$(VMonths),

aggr(sum(Stock_Qty) - ($(VMonths) * AvrgSales),ItemName),))"

But still getting the same error.

Can you please tell me where exactly and how to use aggr function?

Thank You.

[jonathandienst]

Hi

Is AvrgSales a field? If so, does it have only one possible value in the dimension? If not, you will need to use an aggregation function (sum, avg, max...) to get a single possible value.

You may also need to start with Aggr() as mentioned above.

The problem may also be caused by the expansion of VMonths. How is this variable defined?

You also have a stray , near the end of the expression. Is that a typo?

HTH

Jonathan

[alexandros17]

What is the field used as a key to compute Sum(Stock_qty)? that field must be added at the end of Aggr in this way:

Aggr(Sum(..), thatField)

let meknow

[#8]

ItemName is the field used.

In my formula I am using sum many times with each sum I have to use aggr()?

[jonathandienst]

Hi

I think you can do this using something like this:

=Aggr(If(Sum(Stock_Qty) > 0 And Sum(Stock_Qty) > ($(VMonths) * Sum(AvrgSales)), Sum(Stock_Qty) - ($(VMonths) * Sum(AvrgSales))), itemName, [exp date])

But this will make a very ugly dimension, so you might want to group them using a Class() function. Like this:

=Class(Sum(Aggr(If(Sum(Stock_Qty) > 0 And Sum(Stock_Qty) > ($(VMonths) * Sum(AvrgSales)), Sum(Stock_Qty) - ($(VMonths) * Sum(AvrgSales))), itemName, [exp date])) ,100)

(adjust the class size (100 in the example) acccording to your needs and change the field name(s) to the correct ones for your document)

HTH

Jonathan

[alexandros17]

yes