Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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,
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?
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)
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!
I used on the attached file.
Do you have problems in calculated dimension?
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!
I confirm, it is working!
thank you for your help!