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

[#1]

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

SKU	Field A
AA	5
AB	2
BB	6
CC	7

Table 2

SKU	qty
AA	3
AA	2
AA	4
AB	2
CC	1
CC	4

MY EXPECTATION

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

SKU	Field A	sum(qty)	Calculated dimension
AA	5	9	1
AB	2	2	1
BB	6	0	0
CC	7	5	0

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!

[arthur_dom]

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,

[#3]

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

SKU	qty	status
AA	3	open
AA	2	closed
AA	4	closed
AB	2	open
CC	1	open
CC	4	closed

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?

[arthur_dom · Accepted solution]

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)

[#5]

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!

[arthur_dom]

I used on the attached file.

Do you have problems in calculated dimension?

[#7]

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!

[#8]

I confirm, it is working!

thank you for your help!