topic Re: Sum excluding one dimension value from union select in QlikView

Sum excluding one dimension value from union select

Anonymous — Wed, 15 Apr 2015 12:56:31 GMT

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.

Re: Sum excluding one dimension value from union select

morganaaron — Wed, 15 Apr 2015 13:03:03 GMT

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.

Re: Sum excluding one dimension value from union select

PrashantSangle — Wed, 15 Apr 2015 13:08:41 GMT

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,

Re: Sum excluding one dimension value from union select

Anonymous — Wed, 15 Apr 2015 13:22:07 GMT

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

Re: Sum excluding one dimension value from union select

morganaaron — Wed, 15 Apr 2015 13:26:12 GMT

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.