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

[former_member273013]

Hi Tom,

thanks for you reply.

In your example, however, the value of 20190205 must be 1 because precedeeing 3 rows is 20190205, 20190204 and 20190201 (excluding rows with flag = Y).

Is possibile to obtain this result?

Thanks.

Davide

[Tom_N8]

Hi Davide,

OK, I can see what you mean with excluding the Y flags from the calculation. However, 20190205 is not the current date in my example above. In your initial question you state you need the value 1 to be set where the current date flag is equal to N plus the consecutive two previous flags are equal to N as well.

Your comment suggests that you no longer need this against the current date flag only but a (any?) previous date disregarding preceding Y flags (i.e. no longer only the previous 2 consecutive dates) plus any combination of preceding N flags. This would automatically set the value 1 next to any date with flag = N except for the first two dates bearing an N flag:

If this is what you want to achieve then you can simply use the formula:

=If((RunningCount([Date];([Flag];[Customer])) Where([Flag] = "N")) >2) Then 1 Else 0

Can you confirm this really is what you are looking for? I think I still misunderstand your requirement.

Kind regards,

Tom

[former_member273013]

Hi Tom.

thank you very much, your solution is very good!

Today my client explained the requisite of the analysis better: he want to count the number of consecutive working days that the flag "Over Stock" is N.

So I added 2 flag: "Is Weekend" and "Over Stock".

Is possible to include this logic (with 2 flag) in your formula?

Example:

Row 13 is 1 because there are 3 consecutive working days (20190212-20190211-20190208) with flag "Over Stock" = N. Not considered 20190210 and 20190209 because flag "Is Weekend" = Y

Row 7 is 1 because there are 3 consecutive working days (20190206-20190205-20190204) with flag "Over Stock" = N

Row 6 is 0 because there are only 2 consecutive working days (20190205-20190204). 20190202 is weekend and after the customer change

Very Thanks!

Davide