Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Combining expressions on the front end

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


1 Solution

Accepted Solutions
Gysbert_Wassenaar

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]))


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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])

)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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 CodeITEM  CODEJonathan Test
FEEMSFBCP1219189
FEEMSFBCP141348
FEEMSFBCP150
FEEMSFBCP15L0
FEEMSFBCP168876
FEEMSFBCP250
FEEMSFBCP25L0
FEEMSFBCP290
FEEMSFBCP316793
FEEMSFBCPGL40
FEEMSFBCPGL50
FEEMSFBCPMP10
FEEMSFBCPMP2150
FEEMSFBGLEMS0
FEEMSFEEMS0
FEEMSFEEMSGL0
TOTAL

0

Straight Table
Component CodeITEM  CODEJohnathan
FEEMSFBCP1219189
FEEMSFBCP141348
FEEMSFBCP150
FEEMSFBCP15L0
FEEMSFBCP168876
FEEMSFBCP250
FEEMSFBCP25L0
FEEMSFBCP290
FEEMSFBCP316793
FEEMSFBCPGL40
FEEMSFBCPGL50
FEEMSFBCPMP10
FEEMSFBCPMP2150
FEEMSFBGLEMS0
FEEMSFEEMS0
FEEMSFEEMSGL0
TOTAL 36356
Gysbert_Wassenaar

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]))


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you G Wassenaar.  It worked