WebI: Previous Function with Condition

[former_member273013]

Hi All,

I need to perform the following calculation: for each row set to 1 if the current and previous 2 consecutive days have flag = N with the customer as a reset dimension (second column).

In the example it is possible to obtain the result as in the "Value" column?

I tried to use the Previuos Function but always consider the lines with flag = Y

Is it possible to perform this calculation ignoring row with flag = Y?

Thanks to all.

Davide

[Tom_N8]

Hi Davide,

My apologies, I totally disregarded the "consecutive" piece in my previous response. But we can still get to the result by creating another [Helper] variable with the formula =([OS Calc 2]+Previous([OS Calc 2])+Previous([OS Calc 2];2)+Previous([OS Calc 2];3)+Previous([OS Calc 2];4)). Its purpose is to aggregate the occurrences of 1s in [OS Calc 2] over a period of 5 consecutive days. In order for [Value] to be 1 the aggregate must only be 3, i.e. the [Value] variable needs to be enhanced as follows =If([Counter]InList(3;6;9;12;15;18;21) And [Helper]=3) Then 1.

I have tried this with various combinations of consecutive and non-consecutive occurrences of N [Over Stock] indicators and it works.

Looking forward to your response on Monday!

Kind regards,

Tom

[former_member273013]

Hi Tom,

thanks for the support.

I tried your new solution in my case but unfortunately it does not work.

Look this example: I exposed the fields of your solution and the last column (Desideres Value) is the expected result.

- The row 13 must be 1 because there are 3 consecutive working days with Over Stock = N (row 13, 12, 11), rows 10 e 9 is weekend

- The row 12 is correct

- The row 11 must be 1 because there are 3 consecutive working days with Over Stock = N (row 11, 8, 7), rows 10 e 9 is weekend

Is possible obtain this result?

Thanks very much!

Davide