Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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,
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
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.