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,

Thanks a lot for clarifying the requirement.

I think you can achieve what you are attempting with the following formula:

=If((RunningSum((If(If([IsWeekend]="N") Then [Over Stock] Else Replace([Over Stock];"N";"Y") Where([IsWeekend]="Y"))="N" Then 1 Else 0);([Customer])) Where((If(If([IsWeekend]="N") Then [Over Stock] Else Replace([Over Stock];"N";"Y") Where([IsWeekend]="Y"))="N" Then 1 Else 0)=1)) InList (3;6;9;12;15;18;21)) Then 1

This formula is the result of a number of helper variables I created to find a solution. It has all the variable formulas mentioned in steps 1-4 below nested in a single formula:

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

With this variable you turn ‘N’ stock values into ‘Y’ where they occur at a weekend day

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

This simply turns the Ns into 1s and the Ys into 0s

3. Create variable [Counter] =RunningSum([OS Calc 2];([Customer])) Where([OS Calc]="N")

This merely creates a running count of all the 1 values where [Customer] is the reset dimension

4. Finally, create the variable [Value] =If([Counter]InList(3;6;9;12;15;18;21)) Then 1

This variable simply places a 1 where the counter reaches steps of 3, i.e. 3, 6, 9, 12, 15, 18 or 21. As the max value of Ns in a month consisting of 31 days can be 23 (workdays only), the value list stops at 21. If you look at the first table you can see the different steps taken. I also extended your sample to have certainty this works with bigger margins between values.

This is also a different approach to the previous ones as it "groups" your required values into blocks, considering weekends and Y values at the same time.

The screenshot below shows the workings of the different variables (TABLE A) while the same result is achieved with the full formula (TABLE B) as well:

Have a great weekend,

Tom

[former_member273013]

Hi Tom,

Excellent! I had not thought about using the "Replace" function. Veru Thanks for the suggest!

I have only one doubt about your example:

your algorithm work fine in the first 3 cases but in the last case (row 28) the value it should be 0 because there are not 3 consecutive working days (instead, the value of row 20 is correct because there are 3 consecutive working days).

I'm clear enough?

Have a nice weekend to you!

Davide