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 · Accepted solution]

Hi Davide,

This has been an enjoyable but somewhat frustrating journey at the same time 🙂 I believe that we finally get to the desired result with the following changes.

Change both the [Helper] and the [Value] variables to the following formulas:

[Helper]
=If([IsWeekend]="Y") Then 0
ElseIf([OS Calc]="Y") Then 0
ElseIf([OS Calc]="Y" And Previous([OS Calc])="N" And Previous([IsWeekend];2)="Y" And Previous([IsWeekend];3)="Y") Then 0
ElseIf([OS Calc]="N" And Previous([OS Calc])="Y" And Previous([IsWeekend])="N") Then 0
ElseIf([OS Calc]="N" And Previous([OS Calc])="N" And Previous([OS Calc];2)="Y" And Previous([IsWeekend];2)="N") Then 0
ElseIf(([Customer]=Previous([Customer]) And [Customer]=Previous([Customer];2)) And [OS Calc]="N" And Previous([OS Calc])="N" And Previous([OS Calc];2)="N") Then ([OS Calc 2]+Previous([OS Calc 2])+Previous([OS Calc 2];2))
ElseIf(([Customer]=Previous([Customer]) And [Customer]=Previous([Customer];2)) And [OS Calc]="N" And Previous([IsWeekend])="Y" And Previous([IsWeekend];2)="Y") Then (([OS Calc 2]+Previous([OS Calc 2])+Previous([OS Calc 2];2)+Previous([OS Calc 2];3)+Previous([OS Calc 2];4)))
ElseIf(([Customer]=Previous([Customer]) And [Customer]=Previous([Customer];2)) And [OS Calc]="N" And Previous([OS Calc])="N" And Previous([IsWeekend];2)="Y" And Previous([IsWeekend];3)="Y") Then (([OS Calc 2]+Previous([OS Calc 2])+Previous([OS Calc 2];2)+Previous([OS Calc 2];3)+Previous([OS Calc 2];4)))
Else 0

[Value]
=If([Helper]=3) Then 1

I initially wanted to avoid a [Helper] variable with a complex calculation as above but cannot find a better way. Done some testing with this and hope it can be seen as the final solution to the question. Hopefully, I did not forget any other permutations...

I also noticed a small mistake in your last example where rows 10 & 11 should be marked as IsWeekend = Y instead of rows 9 & 10.

You can delete all previously created variables, except for [OS Calc] and [OS Calc 2]. They should have the following definitions:

[OS Calc]
=If([IsWeekend]="N") Then [Over Stock] Else Replace([Over Stock];"N";"Y") Where([IsWeekend]="Y")

[OS Calc 2]
=If([OS Calc]="N") Then 1 Else 0

Really hope this works now!

Kind regards,

Tom

[former_member273013 · Accepted solution]

Hi Tom,

Excellent, your algorithm is very good and it work for the cases that I tested.

Thank you very much, I will propose this solution to my customer.

If my customer want to add other working days (in addition to weekend) what change I do in the formula? (I imagine in the Helper variable).

Thanks again!

Davide