Hi,
I have a dinamic table with Products and Date (Month/Year) as Dimensions and Sales and Cost as Expressions. For calculate the Cost I use the receiving products information, but I don't have receiving every month, so I'm using the function "before ()" to repeat the cost for the months without receiving.
The first problem is to set the Cost script for those columns that still NULL after the command, because any of them have a before() different of NULL; in this case I would like to use the value of the next month, instead of the previous. Thus I have Cost for every Products selected in any Date
The second help I need is to ignore the Date selection for the Cost, but still valid for Sales because if I select a Date where I don't have receiving Products the before () function won't work and my Cost will be NULL.
Follows a Print Screen of the table and the Cost Expression.
if(
isnull(
((sum(if([Entrada - Cód Tipo Entrada/Saída] = '01',([Entrada Item - Valor Detalhado]))))
/
(sum(if([Entrada - Cód Tipo Entrada/Saída] = '01',([Entrada Item - Quantidade Detalhada])))))* [Qtde Liq]
)
,
(
before([CMV])
)
,
((sum(if([Entrada - Cód Tipo Entrada/Saída] = '01',([Entrada Item - Valor Detalhado]))))
/
(sum(if([Entrada - Cód Tipo Entrada/Saída] = '01',([Entrada Item - Quantidade Detalhada])))))* [Qtde Liq]
)