topic Set analysis - Ignore selection in QlikView

Set analysis - Ignore selection

Wed, 04 Aug 2010 09:32:56 GMT

Good day everyone.

I have the following fields: WarehouseCode, QtySold, QtyOnHand, ItemCode.

I have the following expression: (sum({<ItemCode={"F*"} , WarehouseCode={"ZA01"}>} (QtyOnHand))) / (sum({<ItemCode={"F*"}>} (QtySold))/130)

What I want to do is sum all the QtyOnHand for a specifice warehouse (ZA01) and divide it by the QtySold for ALL warehouses (Not only Warehouse ZA01). Note that I only need this for ItemCodes starting with an "F".

Please help me in adjusting my Set analysis in order to accomplish this.

Thanks

Set analysis - Ignore selection

Wed, 04 Aug 2010 09:59:42 GMT

Hi,

Replace the double quotes with single quotes and here is the updated expression,

= (sum({<ItemCode={'F*'},WarehouseCode={'ZA01'}>} QtyOnHand)) / (sum({<ItemCode={'F*'}>} QtySold)/130)

Cheers,

Haneesh

Set analysis - Ignore selection

Wed, 04 Aug 2010 10:18:26 GMT

Thanks Haneesh, but this is not working.

I need the calculation to only take the QtyOnHand of ZA01 & divide it by the QtySold of all the warehouses together (including ZA01).

You see ZA01 is a warehouse that only keeps stock, but does not sell stock.

Hope this makes sense

Set analysis - Ignore selection

Wed, 04 Aug 2010 10:26:32 GMT

To get sales for all warehouses you could do this:

WarehouseCode = {"*"}

or just ignore WarehouseCode in the set by doing this:

WarehouseCode = ,

Try incorportaing one of these into your expression.

Set analysis - Ignore selection

Wed, 04 Aug 2010 10:36:57 GMT

So, I think this would give you something like:

(SUM( {$< ItemCode = {'F*'}, WarehouseCode = {'ZA01'} >} QtyOnHand) /

SUM( {$< ItemCode = {'F*'}, WarehouseCode = {"*"} >} QtySold)) / 130

I'm not sure whether you are dividing the whole expression by 130 or just the 2nd part so you may need to adjust the parentheses.

Set analysis - Ignore selection

Miguel_Angel_Baeyens — Wed, 04 Aug 2010 10:42:57 GMT

Hello Christo,

I may be wrong, but I'd try

(sum({<ItemCode={"F*"} , WarehouseCode={"ZA01"}>} (QtyOnHand))) / (sum({<ItemCode={"F*"}>} TOTAL (QtySold))/130)

specifying TOTAL meaning all QtySold wher ItemCode starts by F regardless the warehouse.

Set analysis - Ignore selection

Wed, 04 Aug 2010 10:50:31 GMT

can you try the following:

<Code>

= (sum({<ItemCode={'F*'},WarehouseCode={'ZA01'}>} QtyOnHand)) / (sum({<ItemCode={'F*'},WarehouseCode=>} QtySold)/130)

</Code>

Set analysis - Ignore selection

Wed, 04 Aug 2010 10:57:04 GMT

Thanks everyone for the help.

Miguel - Your code worked perfect. I tried it just as it is and it worked.

Thanks once again.