Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need help with combining expressions.
Inividual Formulae :
Stock on Hand = sum( if( [Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand])
Stock on Purchase Order = sum([ Qty on PO] )
Stock on Sales Order = sum ([Qty on SO])
The formula i want to create is the following:
Stock Required :
If (Stock on hand - Stock on Purchase Order - Stock on Sales Order < 0 ,0 , Stock on hand - Stock on Purchase Order - Stock on Sales Order.
The formula works when i use the headings , however i want to create the formula using the actual expressions in each of the individual expressions. How do i do this.
Your help would be appreciated.
kind regards
Nayan
Try wrapping that in another sum:
sum(aggr(
RangeMax(0,
sum(if([Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand]))
- Sum([ Qty on PO])
- Sum([Qty on SO])
)
,[Component Code],[ITEM CODE]))
Hi
I would use RangeMax rather than the <0 condition in your final expression. Like this:
RangeMax(0,
sum(if([Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand])
- Sum([ Qty on PO])
- Sum([Qty on SO]))
)
The RangeMax will ensure that -ve values return 0.
HTH
Jonathan
Oops, a misplaced parenthesis:
RangeMax(0,
sum(if([Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand]))
- Sum([ Qty on PO])
- Sum([Qty on SO])
)
Hi Jonathan
Thank you for your repsonse. Your formula works for a chart straight table (by this i mean the total is correct). However , with the pivot table, the total is incorrect. Below is a result which i export to excel.
How do you make the formula work for a pivot table .
I cannot upload my QV model as it is too big and also sensitive information. What i may be able to do create a sample of the model.
kind regards
Nayan
Pivot Table | ||
Component Code | ITEM CODE | Jonathan Test |
FEEMS | FBCP12 | 19189 |
FEEMS | FBCP14 | 1348 |
FEEMS | FBCP15 | 0 |
FEEMS | FBCP15L | 0 |
FEEMS | FBCP16 | 8876 |
FEEMS | FBCP25 | 0 |
FEEMS | FBCP25L | 0 |
FEEMS | FBCP29 | 0 |
FEEMS | FBCP31 | 6793 |
FEEMS | FBCPGL4 | 0 |
FEEMS | FBCPGL5 | 0 |
FEEMS | FBCPMP1 | 0 |
FEEMS | FBCPMP2 | 150 |
FEEMS | FBGLEMS | 0 |
FEEMS | FEEMS | 0 |
FEEMS | FEEMSGL | 0 |
TOTAL | 0 | |
Straight Table | ||
Component Code | ITEM CODE | Johnathan |
FEEMS | FBCP12 | 19189 |
FEEMS | FBCP14 | 1348 |
FEEMS | FBCP15 | 0 |
FEEMS | FBCP15L | 0 |
FEEMS | FBCP16 | 8876 |
FEEMS | FBCP25 | 0 |
FEEMS | FBCP25L | 0 |
FEEMS | FBCP29 | 0 |
FEEMS | FBCP31 | 6793 |
FEEMS | FBCPGL4 | 0 |
FEEMS | FBCPGL5 | 0 |
FEEMS | FBCPMP1 | 0 |
FEEMS | FBCPMP2 | 150 |
FEEMS | FBGLEMS | 0 |
FEEMS | FEEMS | 0 |
FEEMS | FEEMSGL | 0 |
TOTAL | 36356 |
Try wrapping that in another sum:
sum(aggr(
RangeMax(0,
sum(if([Item Data Source]='Jhb - ELLDAT' and [Loc Code IC]='F',0, [Loc Qty On Hand]))
- Sum([ Qty on PO])
- Sum([Qty on SO])
)
,[Component Code],[ITEM CODE]))
Thank you G Wassenaar. It worked