Column Total to equal the Total sum of two other columns with Date Select

[Peter_Brunner]

Hi ,

I am having a issue on how to handle a Horizontal Calculation of the total sum of two other columns.

I have tried several ways to get this to calculate correctly in a summary table and a details table.

I have attached an example with explanation

a fresh set of eyes on this would be appreciated

thanks

Regards Peter

[swuehl]

Maybe like

=[SOH] - Sum(Aggr([SALES_QTY],[Last Sales Date],CATEGORY,STOCK_CODE,STOCK_LOCATION))

[swuehl]

Or just modifiying SALES_QTY to

Sum(Aggr([SALES_QTY],[Last Sales Date],CATEGORY,STOCK_CODE,STOCK_LOCATION))

and using

[SOH]-[SALES_QTY]

[Peter_Brunner]

Hi Stefan,

first thank you for your quick reply and assistance on this.

I have looked at your "Aggr script"  it works but when I Unlock/Clear all selections the Slow Qty totals disappear? 

Also I am trying to replicate these totals in small summary box top left how to Aggregate these

again Much Appreciated Thanks

regards Peter !

[swuehl · Accepted solution]

That's because SOH is not uniquely defined for more than 1 combination of Stock code & location.

How do you aggregate SOH to the total level?

edit:

Well, you don't need to aggregate SOH to the total level, if you are only interested in the total for slow Qty:

New expression for slow QTY:

=Sum(

     Aggr(

          [SOH]-[SALES_QTY]

     ,[Last Sales Date],CATEGORY,STOCK_CODE,STOCK_LOCATION)

)

[Peter_Brunner]

Thanks for your help after reading your comment about "not uniquely defined"  I re analysed my data structure and you are correct! I have re worked my approach and now have it running.

thanks again Stefan