Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

[Peter_Brunner]

Hi Guys,

I have been trying to work out why I cannot get two totals to match

I have two tables:

1)

A straight table with a check Column (expression) which gives correct results for subtracting across columns.

"Notice I am not using any aggregation e.g.: sum , count in expression but this is gives correct results"!

=if((Column(2)-Column(4)-Column(5)-Column(6))<0,0,(Column(2)-Column(4)-Column(5)-Column(6)))

so the straight table columns are pointing to each columns expression as below

(Column(2) = SL_ON_HAND_QTY -

Column(4) =  sum({$<TRANS_TYPE={'DRINV','DRCDT'}>}ENTERED_QTY) -

Column(5)=   sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY) -

Column(6) =  sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'TTT','CCC'}>}ENTERED_QTY)

and another column with =$(vSlowQty) the problem variable.

2)

Now I  have another table an aggregated summary table which is using same variable in expression

=Sum(Aggr($(vSlowQty),STOCK_CATEGORY,STOCK_LOCATION,STOCK_CODE)

as we still need to be able to select on  3 Dimensions which the above formula does

I have used the variable $(vSlowQty) in both tables which is subtracting the Columns as above

so  $(vSlowQty)  =

if(SL_ON_HAND_QTY-

sum({$<TRANS_TYPE={'DRINV','DRCDT'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'TTT','CCC'}>}ENTERED_QTY)<0,0,

SL_ON_HAND_QTY-sum({$<TRANS_TYPE={'DRINV','DRCDT'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'TTT','CCC'}>}ENTERED_QTY))

I have tried using count instead of Sum its gets total closer but still no match what am I missing here ?

any help appreciated

regards Peter

[kaushiknsolanki]

Hi,

Can you attach sample data to understand the issue in detail.

Regards,

Kaushik Solanki

[sunny_talwar]

Your first expression isn't using Sum(Aggr(...)) function, but still giving you the right total, why use it for the second expression? May be try just vSlowQty or $(vSlowQty) as your expression.

[Peter_Brunner]

Hi Guys,

I have attached a sample I put together it has more explanation in app

Sunny I need a variable to reflect the results the way the simple formula below works in main table

if((Column(2)-Column(4)-Column(5)-Column(6))<0,0,(Column(2)-Column(4)-Column(5)-Column(6)))

maybe I should not use set analysis with subtractions ?  but I need to use this in a Variable so I can use this in 2 different tables one of which uses Aggr

thanks Peter

[sunny_talwar · Accepted solution]

Check with this as your variable:

If(

SL_ON_HAND_QTY-

$(vSalesQty)-

Sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'KWH','C33'}>}ENTERED_QTY)-

Sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY)<0,0,

SL_ON_HAND_QTY-

$(vSalesQty)-

Sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'KWH','C33'}>}ENTERED_QTY)-

Sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY))

[Peter_Brunner]

Hi Sunny,

wow that was quick! and Thanks

now explain why this works the way you did this?

Peter

[sunny_talwar]

I think your Column(2) was having an if statement, whereas your variable did not include the if statement within your variable when you were checking if the Col(2) - Col(4) - Col(5) - Col(6) > 0. Once I fixed Col(4) to match what you had in Col(4), the expression started working.

[Peter_Brunner]

Hi Sunny,

I had a good look at what I did and you are right  

thanks again

Peter

[sunny_talwar]

I am glad you were able to grasp the issue that was causing problem here

Best,

Sunny