Sum excluding one dimension value from union select

[Anonymous]

Hi everybody, hope someone can help.

I've loaded a table with 4 fields (DeliveryNote, Item, BatchNumber, Weight ) from a union select stament which one i retrieve data from Purchase a Sales having in common the BatchNumber

Something like that. What I want i a Straigth table with the following structure

I want to achive the sum of the weight for the sales excluding the value of the DeliveryNoet (Purchase). I don´t know how to set the analysis expression.

Thanks in advance.

[morganaaron]

Weight Purchase: Sum({<Type={'Purchase'}>}Weight)

Weight Sales: Sum({<DeliveryNote, Type={'Sale'}>}Weight)

If you don't want the DeliveryNote to apply to Purchase, you can include that field in the set too. Simply stating the field means it will be ignored from the analysis.

[PrashantSangle]

Hi,

In front end

create Straight Table take dimension as required

in Expression

for

1)Purchase:

Sum({<Type={'Purchase'}>}Weight)

2)Sale :

Sum({<Type={'Sale'}>}Weight)

Or in back end modify script like

Load *,Weight as Purchase_Weight from table where type='Purchase'

concatenate

Load *,Weight as Sale_Weight from table where type='Sale'

and use Purchase_weight and Sale_Weight as your new Expression

Regards,

[Anonymous]

Thank you for your time, but I doesn't since the dimension of the DeliveryNote is this one:

=If((Date>=$(minDate) AND Date<=$(maxDate)) AND (Type="Purchase') , DeliveryNote) because I want to show the in the dimensions only the Purchase Delivery Notes, and hence, the rows of the Sales are excluded. 

I would like to get per row the sum of the weigth of the sales with the same BatchNumber that the purchase but avoiding the current selection of the purchase.

I don't know if possible. If not I'll try in the load statement.

Thanks again

[morganaaron]

Oh, apologies I didn't see that the table needs to only have the purchased 'DeliveryNote' in there. What if you created an expression to hold your 'DeliveryNote' value? Then just move the column to the front of the table? Rather than it be  dimension. Then you wouldn't need to worry about the effect it would have on your other expressions - see attached for an example - it does assume you only have one 'DeliveryNote' number per batch/item however.