topic Set Analysis With Variable in QlikView

Set Analysis With Variable

dineshm030 — Wed, 16 Jun 2021 16:47:14 GMT

Hi All,

I have created some text boxes based on the Cell Field Values. Below is the expression.

=Num(Sum(Aggr(If(Cell = 'Rosie 1', $(vOEE)),Cell)),'#,##0.00%')

Without Selection Cell Field Selection values are showing in all text boxes.

if i select one value from Cell field, selected value is showing in text boxes where as remaining its showing as 0.

My requirement is whatever value selected from Cell , all text boxes should be showing their corresponding values.

Kindly help me please.

Thanks in advance.

Re: Set Analysis With Variable

MarcoWedel — Wed, 16 Jun 2021 23:52:52 GMT

maybe something like

Num(Sum({$<Cell={'Rosie 1'}>} $(vOEE)),'#,##0.00%')

what is the content of variable vOEE?

hope this helps

regards

Marco

Re: Set Analysis With Variable

dineshm030 — Thu, 17 Jun 2021 02:55:59 GMT

Hi Macro,

vOEE is

Num(If(Sum([Net Available]) <> 0, Sum(Running)/Sum([Net Available]), 0),'#,##0.00%') *

Num(If(Sum([Pieces per Minute]) * Sum(Running) <> 0, Sum([Actual Production (Pieces)])/(Sum([Pieces per Minute]*Running)),0),'#,##0.00%') *

Num(If(Sum([Actual Production (Pieces)] ) <> 0, (Sum([Actual Production (Pieces)]) - Sum(Reject))/Sum([Actual Production (Pieces)]),0),'#,##0.00%')

Note: I want to extract the particular value from the variable. But we can use set expression in the above formula.

Regards,

Dinesh

Re: Set Analysis With Variable

MayilVahanan — Thu, 17 Jun 2021 03:44:42 GMT

HI Dinesh,

Change the vOEE like below

Num(If(Sum({<$1>}[Net Available]) <> 0, Sum({<$1>}Running)/Sum({<$1>}[Net Available]), 0),'#,##0.00%') *

Num(If(Sum({<$1>}[Pieces per Minute]) * Sum({<$1>}Running) <> 0, Sum({<$1>}[Actual Production (Pieces)])/(Sum({<$1>}[Pieces per Minute]*Running)),0),'#,##0.00%') *

Num(If(Sum({<$1>}[Actual Production (Pieces)] ) <> 0, (Sum({<$1>}[Actual Production (Pieces)]) - Sum({<$1>}Reject))/Sum({<$1>}[Actual Production (Pieces)]),0),'#,##0.00%')

Try like below

=$(vOEE(Cell={'Rosie 1'}))

Re: Set Analysis With Variable

dineshm030 — Thu, 17 Jun 2021 06:37:46 GMT

Hi MayilVahanan,

I have tried your logic but its showing without any selection overall OEE.

If i Select the value from Cell as Rosie 1 both values are same it means for Rosie 2 showing same value.

Rosie 1 Text Box: =$(vOEE(Cell = {'Rosie 1'}))

Rosie 2 Text Box: =$(vOEE(Cell = {'Rosie 2'})) but the real value is 62.22% as given the above image.

Thanks & Regard,

Dinesh.

Re: Set Analysis With Variable

MayilVahanan — Thu, 17 Jun 2021 07:10:24 GMT

Hi Dinesh

Can you send the sample file..

Re: Set Analysis With Variable

dineshm030 — Thu, 17 Jun 2021 08:36:13 GMT

Hi,

PFA...

Thanks in advance...

Re: Set Analysis With Variable

MayilVahanan — Thu, 17 Jun 2021 08:58:12 GMT

Hi Dinesh,

Try like below

Num($(vAvailability($1)) * $(vPerformance($1)) * $(vQuality($1)),'#,##0.00%')

Re: Set Analysis With Variable

dineshm030 — Thu, 17 Jun 2021 14:30:14 GMT

Hi MayilVahanan,

Its working fine.

Thanks & Regards,

Dinesh Kumar.