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

[former_member273013]

Hi Tom,

thank you very much for your support, you have been very professional and kind.

I Accepted you answer.

Best Regards.

Davide

[Tom_N8]

Thank you very much, Davide. I am glad to hear we got there in the end!

Kind regards,

Tom

[Tom_N8]

Hi Davide,

Yes, you will then need to change your [Helper] and [Value] variables, depending on the requirement, e.g. if your client needs to see an indicator for 4 consecutive working days with an 'N' Over Stock indicator. Could you mark the answer above as 'Answered' if it meets your requirement, please?

Kind regards,

Tom

[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

[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

[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

[Tom_N8]

Hi Davide,

Use the Previous() function as follows:

=If((Previous([Flag])="N" And (Previous([Flag];2)="N" And [Flag]="N") And Previous([Customer]) = [Customer])) Then 1 Else 0

This will return Value 1 where your current flag, the previous flag and the previous of that previous flag is equal to 'N':

If you're only looking for a comparison of the current flag with the previous flag, change the above formula to:

=If((Previous([Flag])="N" And [Flag]="N") And Previous([Customer]) = [Customer])) Then 1 Else 0

You can obtain the previous of the previous value by using the offset parameter. In the first formula at the top, this has been set to '2'.

Read more about the Previous() function in the "Using functions, formulas and calculations in Web Intelligence" guide.

Kind regards,

Tom