topic Re: Mode function not working in QlikView

Mode function not working

j_nlucas — Sat, 16 Nov 2024 20:29:47 GMT

Hi,

I need to calculate the MODE of the result of the following expression:

if(MaxString(_CodTipoSeleccion) = 'EF',
((Sum({<_KeyFecha={'>=$(v_FechaInicioSK)<=$(v_FechaFinSK)'},Año,Fecha,Día,DiaMes,Semana,Mes,MesNum,nMes,Trimestre, FchDocumentoVenta= >}MargenBruto) - Sum({<_KeyFecha={'>=$(v_FechaInicioSK)<=$(v_FechaFinSK)'},Año,Fecha,Día,DiaMes,Semana,Mes,MesNum,nMes,Trimestre, FchDocumentoVenta= >}ImpGastos)) / (Sum({<_KeyFecha={'>=$(v_FechaInicioSK)<=$(v_FechaFinSK)'},Año,Fecha,Día,DiaMes,Semana,Mes,MesNum,nMes,Trimestre, FchDocumentoVenta= >}ImporteVenta)- Sum({<_KeyFecha={'>=$(v_FechaInicioSK)<=$(v_FechaFinSK)'},Año,Fecha,Día,DiaMes,Semana,Mes,MesNum,nMes,Trimestre, FchDocumentoVenta= >}ImpGastos)))
,
((Sum(MargenBruto)- sum(ImpGastos)) / (Sum(ImporteVenta)-sum(ImpGastos)))
)

I wrapped all that into a variable that I've used in the Mode function but I keep getting nulls.

I'm using it in a straight table with three dimensions.

Any idea?

Thank you

Re: Mode function not working

jfkinspari — Tue, 09 Jul 2019 10:39:55 GMT

Perhaps you have included a "=" and are calculating the variable before it's input into the straight table

Re: Mode function not working

j_nlucas — Tue, 09 Jul 2019 10:42:25 GMT

Hi Jfkinspari, no equal included.

Re: Mode function not working

jfkinspari — Tue, 09 Jul 2019 10:45:27 GMT

Do you get a result if you use any other aggregation function, e.g. count?

Re: Mode function not working

j_nlucas — Tue, 09 Jul 2019 10:47:37 GMT

No I don't

Re: Mode function not working

jfkinspari — Tue, 09 Jul 2019 10:49:46 GMT

Okay, then I suggest you backtrack the problem, by moving the expression from the variable into the table itself, and find out when it starts to fails

Re: Mode function not working

j_nlucas — Tue, 09 Jul 2019 10:53:07 GMT

The expression works well with and without the variable in the table.

The problem arises when I apply an aggregation function to it. I am not sure I to backtrack that from there...

Re: Mode function not working

j_nlucas — Tue, 09 Jul 2019 10:56:33 GMT

I have now reduced the expression to this:

Mode(Sum(MargenBruto))

and it's still returning null values

Re: Mode function not working

jfkinspari — Tue, 09 Jul 2019 11:07:15 GMT

try using RangeMode(Sum(MargenBruto))

Re: Mode function not working

j_nlucas — Tue, 09 Jul 2019 11:10:31 GMT

That works!!

Thank you so much for your help. It's really appreciated!

Re: Mode function not working

jfkinspari — Tue, 09 Jul 2019 11:16:51 GMT

Great 🙂

please remember to mark the post as solved.