topic Re: Partial Sums Incorrect on the Expression in QlikView

Partial Sums Incorrect on the Expression

Fri, 22 Aug 2014 16:17:59 GMT

Item Key	Lbs Needed	On Hand Lbs	Lbs Needed after On Hand
Dimension	Expression	Expression	Expression
	=Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)	=SUM(distinct Qty*ConvtoLbs)	= if ( Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQtyConvtoLbs) - sum(distinct QtyConvtoLbs) < 0 , 0 , Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQtyConvtoLbs) - sum(distinct QtyConvtoLbs) )


ABC	23	2	21
PQR	52	0	52
XYZ	72	150	0
Totals	147	152	-5

I have above chart and the totals are not being calculated correctly . Like above the totals should be calculating as 73 . It does not seems to be recognizing zero from XYZ product and its adding the negative figure (72-150 = -78) to the total and showing 73-78 = -5 as total. I tried using the aggr function and this is what I get after that.

ItemKey	Lbs Needed	On Hand Lbs	Lbs Needed after On Hand
Dimension	Expression	Expression	Expression
	=Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)	=SUM(distinct Qty*ConvtoLbs)	= aggr (if ( Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQtyConvtoLbs) - sum(distinct QtyConvtoLbs) < 0 , 0 , Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQtyConvtoLbs) - sum(distinct QtyConvtoLbs) ),ItemKey)
ABC	23	2	21
PQR	52	0	-
XYZ	72	150	0
Totals	147	152	-

Wherever the On Hand Lbs is equal or more than Lbs Needed , I need to show Lbs Needed after on Hand as 0, which I was able to get that if I don't use aggr function but how could I get the totals to appear correctly? Thanks in advance!

Re: Partial Sums Incorrect on the Expression

jerem1234 — Fri, 22 Aug 2014 17:28:00 GMT

Try:

sum(aggr (if (

Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)

- sum(distinct Qty*ConvtoLbs) < 0 , 0 ,

Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)

- sum(distinct Qty*ConvtoLbs) ),ItemKey))

Hope this helps!

Re: Partial Sums Incorrect on the Expression

Fri, 22 Aug 2014 19:38:16 GMT

Thanks Jermiah , that puts in the correct total but in some cases 'Lbs Needed after on Hand' is not being calculated properly. Continuing with the example , I added on more data set to explain further, like for PQR 'Lbs Needed after on Hand' shows 0 instead of showing 52

ItemKey	Lbs Needed	On Hand Lbs	Lbs Needed after On Hand
Dimension	Expression	Expression	Expression
	=Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)	=SUM(distinct Qty*ConvtoLbs)	= sum(aggr (if ( Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQtyConvtoLbs) - sum(distinct QtyConvtoLbs) < 0 , 0 , Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQtyConvtoLbs) - sum(distinct QtyConvtoLbs) ),ItemKey))
ABC	23	2	21
PQR	52	0	0
XYZ	72	150	0
STU	42	0	42
Totals	189	152	63

Re: Partial Sums Incorrect on the Expression

jerem1234 — Fri, 22 Aug 2014 19:49:39 GMT

Not sure, it should be working. Do you have more than one dimension in your chart?

if you could, post a sample app that demonstrates the problem.

Re: Partial Sums Incorrect on the Expression

Anonymous — Fri, 22 Aug 2014 19:51:48 GMT

did you try taking out the aggr altogether?

if this is a straight table with ItemKey as dimension and other two columns as expression, then the third column can be simply the expression in your original post. for that expression, in total mode, choose sum of rows instead of expression total.

Re: Partial Sums Incorrect on the Expression

Fri, 22 Aug 2014 20:29:52 GMT

Thanks I converted the chart from pivot table to straight table and sum of rows under Total Mode worked for me