topic Re: Formula in Calculated Dimension in QlikView

Formula in Calculated Dimension

Thu, 04 Dec 2014 13:03:55 GMT

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

Re: Formula in Calculated Dimension

alexandros17 — Thu, 04 Dec 2014 13:07:34 GMT

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

Re: Formula in Calculated Dimension

Thu, 04 Dec 2014 13:16:15 GMT

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

Re: Formula in Calculated Dimension

alexandros17 — Thu, 04 Dec 2014 13:26:43 GMT

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?

Re: Formula in Calculated Dimension

Thu, 04 Dec 2014 13:39:12 GMT

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:

if Stock_Qty >0
if (sum(Stock_Qty)/AvrgSales)>$(VMonths)
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.

Re: Formula in Calculated Dimension

jonathandienst — Thu, 04 Dec 2014 13:39:18 GMT

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

Re: Formula in Calculated Dimension

alexandros17 — Thu, 04 Dec 2014 13:45:04 GMT

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

Re: Formula in Calculated Dimension

Thu, 04 Dec 2014 13:48:31 GMT

ItemName is the field used.

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

Re: Formula in Calculated Dimension

jonathandienst — Thu, 04 Dec 2014 13:51:28 GMT

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

Re: Formula in Calculated Dimension

alexandros17 — Thu, 04 Dec 2014 13:51:45 GMT

yes

Re: Formula in Calculated Dimension

Thu, 04 Dec 2014 13:54:27 GMT

Yes each ItemName has one AvrgSales loded in Qlikview this value is unique by ItemName and dose not change. Actually it is an estimation of the sales.

VMonths is defined using a slider in QlikView.

You mean " by stray?

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