topic Re: Set Analysis using variable in QlikView

Set Analysis using variable

matancha — Tue, 28 Jan 2014 09:25:14 GMT

Hello all.

I'm develop an OOR (Open Order Report), and I have a problem....

My Background table (that loaded on script):

Open Orders

Warhouses Balance

I want to show a table with all the open orders details, and the corresponding current delivery warhouse balance.
On Open Orders table I have a fields that contain the delivery wrhs:

OpenOrders.DeliveryWarehouseName

On Warhouses Balance, field that contain the name of the warehouse.

Warehouse#

My idea was to define a variables that contain the OpenOrders.DeliveryWarhouseName:

vDeliveryWrhsName = OpenOrders.DeliveryWarehouseName

and by Set Analysis to wrote somthing like:

Sum( {< Warehouse# = {'$(vDeliveryWrhsName)' >} WarehousesBalance.Balance ).

and it's not working for me...

My problem is how to declare the variable (maybe more than 1?), and how to use them in the chart (straight or pivot tables).

sopry for my poor english,

hope that is clear.

Thanks in advance -

Matan.

Re: Set Analysis using variable

er_mohit — Tue, 28 Jan 2014 09:34:08 GMT

Try like this

Sum( {< WarehouseField = {'=$(=vDeliveryWrhsName)'} >} WarehousesBalance.Balance ).

Re: Set Analysis using variable

matancha — Tue, 28 Jan 2014 09:58:43 GMT

Mohit -

Thanks for your quick response.

Unfortunately the solution you gave does not work for me...

Thanks again -

Matan.

Re: Set Analysis using variable

matancha — Tue, 28 Jan 2014 10:03:37 GMT

Something more:

I think - the fact that every open order row is linked (through the part) to many warehouses - causing the problem.

I need an expression to sum only the delivery warehouse.

Matan.

Re: Set Analysis using variable

er_mohit — Tue, 28 Jan 2014 10:06:14 GMT

then try this if u indeed it to hard code then

Sum( {< WarehouseField = {'DeliveryWrhsName'} >} WarehousesBalance.Balance ).

Re: Set Analysis using variable

matancha — Tue, 28 Jan 2014 10:16:25 GMT

I think that I'm missing something -

maybe the problem is with the variable?

How to set the variable?

With the '=' sign? Without?

Re: Set Analysis using variable

er_mohit — Tue, 28 Jan 2014 10:18:00 GMT

with = sign

Re: Set Analysis using variable

vipin_mishra479 — Tue, 28 Jan 2014 10:37:51 GMT

Hi,

use this

create variable like

vVariable = Concat(DISTINCT {<WarehouseField = {'OpenOrders.DeliveryWarehouseName'}>} chr(39)&WarehouseField &chr(39))

and put this in set analysis expression

Sum( {< WarehouseField = {'$(vVariable)'} >} WarehousesBalance.Balance )

vipin

Re: Set Analysis using variable

matancha — Wed, 29 Jan 2014 06:12:06 GMT

OK pepole,

Now I'm have an expression that work for me, but - it's without Set Analysis:

Sum(If( (Warehouses.Db &'-'& Warehouse#) = (OpenOrders.DB &'-'& OpenOrders.DeliveryWarehouseName), (WarehousesBalance.Balance) ))

How can i write it with Set Analysis??

Thanks in advance -

Matan.

Re: Set Analysis using variable

vipin_mishra479 — Wed, 29 Jan 2014 06:24:17 GMT

Hi Mantan,

would u please attached any example.

vipin

Re: Set Analysis using variable

matancha — Wed, 29 Jan 2014 06:32:21 GMT

Hi Vipin -

Currently - I don't think I can attach the original file (its a big file, with a lot of commercial data...)

Matan.

Re: Set Analysis using variable

vipin_mishra479 — Wed, 29 Jan 2014 06:35:18 GMT

make some related example file.

Re: Re: Set Analysis using variable

matancha — Wed, 29 Jan 2014 07:58:34 GMT

Hi Vipin.

I attached a file with an example.

Please look at the "Delivery Wrhs Qty Balance" - (yellow chart column) .

How can I write it with Set Analysis?

Please note that currently there is 1 DB (qob), and in reality there is more (if this matter),

Thanks (again...) for your help -

Matan.