Partial Sums Incorrect on the Expression

[#1]

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))"}>} OrdQty*ConvtoLbs)    - sum(distinct Qty*ConvtoLbs) < 0 , 0 ,    Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)    - sum(distinct Qty*ConvtoLbs) )
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))"}>} OrdQty*ConvtoLbs)    - sum(distinct Qty*ConvtoLbs) < 0 , 0 ,    Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)    - sum(distinct Qty*ConvtoLbs) ),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!

[jerem1234]

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!

[#3]

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))"}>} OrdQty*ConvtoLbs)    - sum(distinct Qty*ConvtoLbs) < 0 , 0 ,    Sum({<Dt= {"<=$(= Max(Dt))"}>} OrdQty*ConvtoLbs)    - sum(distinct Qty*ConvtoLbs) ),ItemKey))
ABC	23	2	21
PQR	52	0	0
XYZ	72	150	0
STU	42	0	42
Totals	189	152	63

[jerem1234]

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.

[Anonymous · Accepted solution]

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.

[#6]

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