If NULL value use the previous month value

[#1]

Hi,

I'm trying to use an expression where under determinated condition it calculate the product cost (value/qty), but in the months I dont receive the product the cost is NULL, so I'm trying to set this expression to use the previous month value when the original value is NULL.

Any ideas?

Here follows the 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])))))

)

,

(Here goes previous month command, for NULL values)

,

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

)

[#2]

Hi,

If you have a table and you want the value in the previous row, you sould check the above function, if you want to use the value of the actual month -1 then you can try this:

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

)

,

sum($<[Entrada - Cód Tipo Entrada/Saída] = {'01'}, Month = {$(max(Month)-1)}> [Entrada Item - Valor Detalhado])

/

sum($<[Entrada - Cód Tipo Entrada/Saída] = {'01'}, Month = {$(max(Month)-1)}> [Entrada Item - Quantidade Detalhada])

,

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

)

Hope this helps

Regards!

[#3]

Hi Gabriela,

Thank you for your help.

Actually the best scenario would be use the previous row value (or more than one row if I want to insert more rows, like sales and changes), thus I wouldn't need to set any exception for this new value as long as the exception was already set for the value that will be repeated.

I've attached a print screen of this table to have an idea about what I'm meaning.

I'm using the Qlikview 8.5 version, so I can't any exclusive new command.