Ignoring Dimension [MesAno Comercial] doesn´t work

[felisberto_joao]

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

[Anil_Babu_Samineni]

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])

[felisberto_joao]

Your sugestion didn´t work

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

[Anil_Babu_Samineni]

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

[felisberto_joao]

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)

[jonathandienst]

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.

[felisberto_joao]

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

[jonathandienst]

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.