Problems with Set Analysis

[ivangarcia]

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.

[swuehl]

What's the definition of all your variables?

[ivangarcia]

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!

[swuehl]

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)

[ivangarcia]

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!