topic Ignoring Dimension [MesAno Comercial] doesn´t work in QlikView

Ignoring Dimension [MesAno Comercial] doesn´t work

felisberto_joao — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

When you select the dimension [MesAno Comercial] the result is null. I´m ignoring the dimension but it doesn´t work. Can anyone help?

Dimension:

=aggr(if((sum({$<[Data Comercial]={'$(=max(Date([Data Comercial])))'},[MesAno Comercial]=>}[Val Estoque venda])/

sum({$<[Data Comercial]={'>=$(=Date(DataMenos30D))'},[MesAno Comercial]=,[Tpo]-={$(TPODev)}>}valvenda)<=0.5)

and (sum({$<[Data Comercial]={'>=$(=Date(DataMenos90D))'},[MesAno Comercial]=>}[Qt pedido compra]) <=0),CodDescr,Null()), CodDescr)

Column:

=sum({<[Data Comercial]={'=$(=max(Date(DataAtual)))'},[MesAno Comercial]=>}[Qt. Disp]*[Preço venda])

Thanks,

Felisberto

Re: Ignoring Dimension [MesAno Comercial] doesn´t work

Anil_Babu_Samineni — Thu, 23 Nov 2017 14:25:49 GMT

May be this?

Dimension:

=aggr(if((sum({$<[Data Comercial]={'$(=max(Date([Data Comercial])))'},[MesAno Comercial]=>}[Val Estoque venda])/

sum({$<[Data Comercial]={'>=$(=Date(DataMenos30D))'},[MesAno Comercial]=,[Tpo]-={$(TPODev)}>}valvenda)<=0.5)

and (sum({$<[Data Comercial]={'>=$(=Date(DataMenos90D))'},[MesAno Comercial]=>}[Qt pedido compra]) <=0),CodDescr,'Null'), CodDescr)

Column:

=sum({<[Data Comercial]={$(=max(Date(DataAtual)))},[MesAno Comercial]=>}[Qt. Disp]*[Preço venda])

Re: Ignoring Dimension [MesAno Comercial] doesn´t work

felisberto_joao — Thu, 23 Nov 2017 14:48:17 GMT

Your sugestion didn´t work

It´s works only if there no selection or if you select the max dimension (201711)

Re: Ignoring Dimension [MesAno Comercial] doesn´t work

Anil_Babu_Samineni — Thu, 23 Nov 2017 16:34:54 GMT

Try to use MonthYear and expression first and then we can manipulate those

Re: Ignoring Dimension [MesAno Comercial] doesn´t work

felisberto_joao — Fri, 24 Nov 2017 09:49:21 GMT

I think it´s more something like this but still doesn´t work.Look at the thread:

Ignoring specific dimension in AGGR function | Qlik Community

=aggr({<[MesAno Comercial]=>}if((sum({$<[Data Comercial]={'$(=max(Date([Data Comercial])))'},[MesAno Comercial]=>}[Val Estoque venda])/

sum({$<[Data Comercial]={'>=$(=Date(DataMenos30D))'},[MesAno Comercial]=,[Tpo]-={$(TPODev)}>}valvenda)<=0.5)

and (sum({$<[Data Comercial]={'>=$(=Date(DataMenos90D))'},[MesAno Comercial]=>}[Qt pedido compra]) <=0)

,CodDescr,Null()), CodDescr)

Re: Ignoring Dimension [MesAno Comercial] doesn´t work

jonathandienst — Fri, 24 Nov 2017 10:23:20 GMT

The possible values of the dimensions in the Aggr() (CodDescr) reduce the Aggr() before the set expressions in the Sum()s are executed. The only workaround for a dimension in this case is to create the dimension as a field in the back end.

When the Aggr() is inside and aggregating function, like Sum(), then the outer Sum's set expression is executed before the Aggr() and you can then override the selections. A 'naked' Aggr(), however, is going to respond to selections and there is no way to override that AFAIK.

Re: Ignoring Dimension [MesAno Comercial] doesn´t work

felisberto_joao — Fri, 24 Nov 2017 17:08:28 GMT

Hi Jonathan,

Is there an another way to calculate the dimension? May be without Aggr() to return the desirred result?

If there isn´t solution i will do it on load but i prefer to do it on view mode.

Thanks.

Felisberto

Re: Ignoring Dimension [MesAno Comercial] doesn´t work

jonathandienst — Mon, 27 Nov 2017 04:34:43 GMT

Maybe you should consider not using a calculated dimension, and add the appropriate set filter to the expressions. In general, I would expect this to perform better, especially so in a large data model. It may be easier with some minor back end mods, such as pre-calculating

sum({$<[Data Comercial] = {'$(=Max(Date([Data Comercial])))'}, [MesAno Comercial]>} [Val Estoque venda]) /

sum({$<[Data Comercial] = {'>=$(=Date(DataMenos30D))'}, [MesAno Comercial], [Tpo] -= {$(TPODev)}>} valvenda)

I am assuming that the values DataMenos30D, DataMenos90D and TPODev are variables and not fields. If that is not true, then the set expression may not work at all. If they have more than one possible value, they will evaluate to null() and so willl not filter in the way that you intend.