Hello everybody, this is my first post and I come to you with a smart question.
I'm searching for a solution to this problem: I have a set of data coming from stock balances. My app must show the amount of available quantities for each Item.
The simplest expression would be [Stock Qty] - [Ordered Qty] = [Available Qty] that will be coded as Sum ([Stock Qty] - [Ordered Qty]).
Now comes the tricky part. Since I don't want any negative value my expression should be:
if ((Sum ([Stock Qty] - [Ordered Qty]) < 0, 0, Sum ([Stock Qty] - [Ordered Qty])) and at the lower aggregation level it works fine, but when it comes to do it to a higher level it doesn't make the job done.
I have this scenario:
Warehouse
Item
Stock Balance
Ordered Qty
Available Qty
MAIN
A
10
12
0
MAIN
B
15
14
1
MAIN
C
2
0
2
Total
27
26
3
SECONDARY
B
10
5
5
SECONDARY
C
5
11
0
Total
15
16
5
If I create a pivot table or a Bar Chart based on Warehouse field in Dimension and Available Qty field in measure, the result is the following:
Warehouse
Available Qty
MAIN
1
SECONDARY
0
But i would like to be:
Warehouse
Available Qty
MAIN
3
SECONDARY
5
Is there a way to tell the system to always sum at the lowest level to obtain the above values.
Thank you Sunny for your quick reply, I was not familiar with the aggr() function. I read about it on reference manuals, but not sure on how to apply it to my projects, now it looks more clear.
