topic Re: Combining expressions on the front end in QlikView

Combining expressions on the front end

Thu, 17 Oct 2013 08:06:48 GMT

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

Re: Combining expressions on the front end

jonathandienst — Thu, 17 Oct 2013 08:22:53 GMT

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

Re: Combining expressions on the front end

jonathandienst — Thu, 17 Oct 2013 08:24:06 GMT

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

)

Re: Combining expressions on the front end

Thu, 17 Oct 2013 09:01:02 GMT

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

Re: Combining expressions on the front end

Gysbert_Wassenaar — Thu, 17 Oct 2013 10:43:45 GMT

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

Re: Combining expressions on the front end

Thu, 17 Oct 2013 11:24:24 GMT

Thank you G Wassenaar. It worked