topic Re: Set Analysis & Aggr problem in QlikView

Set Analysis & Aggr problem

ecolomer — Sat, 30 Jul 2016 18:37:01 GMT

Hi,

I have a table that presents:

Customers who have had

sales in 2016,
which had in 2015 (same period),
sales in 2016 and
sales in 2015 and
I want to get (the data shown are not well) a column with customers who increased and customers who have decreased their sales volume more than on e% that you can choose value (vVaria)

Attached file qv

Formula in Suben for Acumulated are:

=Sum(if(aggr(Sum({<Año={$(AñoActual)}, Mes={'<=$(MesActual)'}>} imVentaTAM) - (1+vVaria) * Sum({<Año={$(AñoAnterior)}, Mes={'<=$(MesActual)'}>} imVentaTAM), Cliente) >0, 1,0))

Saludos,

Enrique

Re: Set Analysis & Aggr problem

swuehl — Sun, 31 Jul 2016 16:19:51 GMT

I think you need to filter customers using an outer aggregation set analysis that made sales in both years, something like

=Sum( {<Cliente = {"=Sum({<Año={$(AñoActual)}, Mes={'=$(MesActual)'}>} imVentaTAM) >0 AND Sum({<Año={$(AñoAnterior)}, Mes={'=$(MesActual)'}>} imVentaTAM)>0"} >}

aggr(If(

Sum({<[Año]={$(AñoActual)}, Mes={'=$(MesActual)'}>} imVentaTAM) - (1+vVaria) * Sum({<[Año]={$(AñoAnterior)}, Mes={'=$(MesActual)'}>} imVentaTAM)>0,1,0), Cliente)

)

Re: Set Analysis & Aggr problem

ecolomer — Sun, 31 Jul 2016 17:05:55 GMT

I try with this:

=Sum({<Año={$(AñoActual), $(AñoAnterior)}, Mes={'<=$(MesActual)'}>}

if(aggr(Sum({<Año={$(AñoActual)}, Mes={'<=$(MesActual)'}>} imVentaTAM) -

(1+vVaria) * Sum({<Año={$(AñoAnterior)}, Mes={'<=$(MesActual)'}>} imVentaTAM), Cliente) >0, 1,0)

)

¿is equivalent? or changing AND by OR in your expresion. But I have diferent valors in each option

Re: Set Analysis & Aggr problem

swuehl — Sun, 31 Jul 2016 20:53:40 GMT

No, I don't think it's equivalent (and you also get different results, right?).