topic Re: Problems with Set Analysis in QlikView

Problems with Set Analysis

ivangarcia — Mon, 26 Dec 2016 19:07:58 GMT

Hello everyone, I have a small problem with Set Analysis that I hope someone can support me with their experience. I have the following formula applied in the following way so that it shows me the value that corresponds to the 'Distrito', but that value does not have movement in the board regardless of the selection that is made.

=(Count({1<dto={'Distrito IE'}>}if(ret_cumple_politica='N',ret_importe))/Count({1<dto={'Distrito IE'}>}ret_cumple_politica))*vRetiros

(((Sum({1<aj_motivo={'SALIDA POR CONVERSION (BOTADERO)'}>} {1<dto={'Distrito IE'}>}aj_importe)

+Sum({1<aj_motivo={'SALIDA POR CORRECCION A AJUSTE DE INVENTARIO'}>} {1<dto={'Distrito IE'}>}aj_importe)-

(Sum({1<aj_motivo={'ENTRADA POR CONVERSION (BOTADERO)'}>} {1<dto={'Distrito IE'}>}aj_importe)

+Sum({1<aj_motivo={'ENTRADA POR CORRECCION A AJUSTE DE INVENTARIO'}>} {1<dto={'Distrito IE'}>}aj_importe)))/Sum({1<dto={'Distrito IE'}>}proyectado))*100)*vAjustes

(((Sum({1<dto={'Distrito IE'}>}dev_total))/Sum({1<dto={'Distrito IE'}>}venta_diaria))*100)*vDevolucion

(((Sum({1<dto={'Distrito IE'}>}bon_total))/Sum({1<dto={'Distrito IE'}>}proyectado))*100)*vBonificacion

((Sum({1<dto={'Distrito IE'}>}vFueraPolFalt))/(Sum({1<dto={'Distrito IE'}>}vSobrMonto)+(Sum({1<dto={'Distrito IE'}>}vFaltMonto))))*vFaltante

((Sum({1<dto={'Distrito IE'}>}vFueraPolSobr))/(Sum({1<dto={'Distrito IE'}>}vSobrMonto)+(Sum({1<dto={'Distrito IE'}>}vFaltMonto))))*vSobrante

((Sum({1<dto={'Distrito IE'}>}mer_importe))/Sum({1<dto={'Distrito IE'}>}proyectado)*100)*vMermas

(SUM({1<dto={'Distrito IE'}>}cte_saldo)*100/sum({1<dto={'Distrito IE'}>}venta_diaria)*vCreditos)

The problem is that the value is modified with any selection, and the indicator is required to be static.

In advance I am grateful for the support.

Best regards.

Re: Problems with Set Analysis

swuehl — Mon, 26 Dec 2016 21:16:22 GMT

What's the definition of all your variables?

Re: Problems with Set Analysis

ivangarcia — Mon, 26 Dec 2016 22:14:59 GMT

Hi Stefan thanks for replying, the variables are as follows.

vRetiros = 0.1

vAjustes = 0.1

vDevolucion = 0.15

vBonificacion = 0.15

vFaltante = 0.05

vSobrante = 0.05

vMermas = 0.15

vCreditos = 0.2

vFueraPolSobr =Count(if(sob_monto >= '10', sob_monto))

vFueraPolFalt =Count(if(fal_monto >= '10', fal_monto))

vSobrMonto =Count(sob_monto)

vFaltMonto =Count(fal_monto)

Regards!

Re: Problems with Set Analysis

swuehl — Tue, 27 Dec 2016 00:37:18 GMT

Not sure if I really understand what you are trying to do.

I currently see some potential issues with your expression:

- If you create a variable like

vFaltMonto =Count(fal_monto)

then I don't understand why you are using this variable in

(Sum({1<dto={'Distrito IE'}>}vFaltMonto))

Are you trying to sum a constant value? Maybe you need to apply the set analysis also to the aggregation in the variable definition then, if you want to keep it constant regardless of selections.

- You sometimes use two set expressions in one aggregation, like in

(Sum({1<aj_motivo={'SALIDA POR CONVERSION (BOTADERO)'}>} {1<dto={'Distrito IE'}>}aj_importe)

I don't think this will work as expexted, if you want to apply both filters, you should use

(Sum({1<aj_motivo={'SALIDA POR CONVERSION (BOTADERO)'}, dto={'Distrito IE'}>}aj_importe)

Re: Problems with Set Analysis

ivangarcia — Tue, 27 Dec 2016 20:38:22 GMT

The variables I am using in this case to simplify the syntax of the formulas.

If I have used both options that you show me at the end, but in both cases the result is influenced by the selection that is made.

Previously I had the following formula,

=Num(((((Count({1<dto={'Distrito ID'}>}if(ret_cumple_politica='N',ret_importe))/Count({1<dto={'Distrito ID'}>}ret_cumple_politica))*vRetiros //RETIROS

Sum({1<dto={'Distrito ID'}>}aj_importe)*100/Sum({1<dto={'Distrito ID'}>}venta_diaria)/100*vAjustes //AJUSTES

Sum({1<dto={'Distrito ID'}>}dev_total)*100)/Sum({1<dto={'Distrito ID'}>}venta_diaria)/100*vDevolucion //DEVOLUCIONES

Sum({1<dto={'Distrito ID'}>}bon_total)*100/Sum({1<dto={'Distrito ID'}>}venta_diaria)/100*vBonificacion //BONIFICACIÓN

Sum({1<dto={'Distrito ID'}>}fal_tipo='FALTANTE' AND fal_monto >= 10)*-1/100*vFaltante //FALTANTE

Sum({1<dto={'Distrito ID'}>} sob_tipo ='SOBRANTE'AND sob_monto >= 10)*-1/100*vSobrante //SOBRANTE

Sum({1<dto={'Distrito ID'}>} mer_importe)*100/Sum({1<dto={'Distrito ID'}>} venta_diaria)/100*vMermas //MERMAS

SUM({1<dto={'Distrito ID'}>}cte_saldo)*100/sum({1<dto={'Distrito ID'}>}venta_diaria)*vCreditos)/7)*100,'##.##%') //CREDITOS

and the Set Analysis worked correctly, but I had to change it and that's when the problems started.

Maybe I'm doing a bad practice with the variables, but what I want is to simplify the formulas. If so, I would like to know some other option to avoid using the variables for this purpose.

Regards!