7 Replies Latest reply: Aug 30, 2013 9:06 AM by Erik Furlanis

Calculated dimension: SUM is yielding error (with or without IF statement)

Dear all

This is the nth topic about calculated dimensions and summing and if and aggregation...

I am trying to create a calculated dimension which check whether the sum of qty is higher than the value which is on field A.

Data in the pivot chart is shown per SKU levels.

Qty data comes from lines, therefore there are several lines per SKU (in the backgroung)

Meanwhile data in field A is one single value per SKU

SOURCE DATA

Table 1

SKUField A
AA5
AB2
BB6
CC7

Table 2

SKUqty
AA3
AA2
AA4
AB2
CC1
CC4

MY EXPECTATION

calculated dimension: if ( sum(qty)>=Field A, 1, 0)

SKUField Asum(qty)Calculated dimension
AA591
AB221
BB600
CC750

So I hope this example is pretty self explanatory.

I am having trouble with the calculated dimension, as apparently it cannot handle the Sum of different lines, let alone the IF statement.

If I try sum(qty) or aggr(sum(qty)) or other weird combinations, it always yields

//Error in calculated dimension.

Can please someone help me to crack this? I tried applying solution of several other threads, but indeed I'm still at square one

thank you so much!

• Re: Calculated dimension: SUM is yielding error (with or without IF statement)

Hi Erik.

I get your tables and done it on the attached qvw, please see if you understand.

the formula i used is "=aggr( if(sum(qty) >= [Field A], 1, 0), SKU)"

Best regards,

• Re: Re: Calculated dimension: SUM is yielding error (with or without IF statement)

thank you so much, I reckon now that I was simply forgetting the "group by parameter" (SKU).

One last question:

let's assume that the lines table is

SKUqtystatus
AA3open
AA2closed
AA4closed
AB2open
CC1open
CC4closed

and I want to check the same condition

=aggr( if(sum(qty) >= [Field A], 1, 0), SKU)

but only in case the status='closed' .

Is there a way to achieve this, or this can be solved just by using Set Analysis and thus in an expression and not in calculated dimension?

• Re: Re: Calculated dimension: SUM is yielding error (with or without IF statement)

Sorry Erik, i didn't get the idea...

You want :

a) Only Sum when status is closed

b) only sum products that have one or more status closed.

if is a) you can use set analysis

aggr( if(sum({<status={'closed'}>}   qty) >= [Field A], 1, 0), SKU)

if is b) probably you will have to use count with set analysis something like:

aggr( if( sum ( sum(qty) >= [Field A] AND aggr(count({<status={'closed'}>} SKU),SKU) = 0, 1, 0 ), SKU)

• Re: Re: Calculated dimension: SUM is yielding error (with or without IF statement)

It is the first one I want

a) Only Sum when status is closed

but anyway in both cases, I would need to input that in the expression, as Set Analysis cannot be used in dimension, right?

thank you again for all of your support!

• Re: Re: Re: Calculated dimension: SUM is yielding error (with or without IF statement)

I used on the attached file.

Do you have problems in calculated dimension?

• Re: Re: Calculated dimension: SUM is yielding error (with or without IF statement)

I notice!

it was just an inquiry, as I think I was reading some time ago that you cannot use Set Analysis in a calculated dimension.

Will let you know the outcome

thx!

• Re: Re: Re: Calculated dimension: SUM is yielding error (with or without IF statement)

I confirm, it is working!