For Loop between two dates and counting when a sales person was at a customer

[CorvoAttano]

Hello Community,

I am quiet new to Qlik Sense and dont know how to use the correct for loop here.

The following requirement is given:

All customers have to be visited in a specific frequency. If the customer was not visited in that defined frequency, it has to be checked. 
A sales person is visiting a customer in a certain period. That is defined with the field "Frequency". 
"Frequency" contains these values:

1 = 1x per week

2 = Every two weeks

3 = Every three weeks

4 = Every four weeks

5 = twice a week

I defined a flag "Flag_Visit" when the customer was visited on one day (1 = was visited, 0 = was not visited). 

A customer only has one frequency and I want to display in the end, if the customer was visited as discussed.

Im banging my head against the wall trying to understand how to use here the correct syntax. Can someone please help me with this issue?

Thank you in advance!!

[vikasmahajan]

hi ,

I think you need to implement a calender in your script refer blog

https://www.qlikviewaddict.com/2013/03/dates-in-qlikview-part-3.html

using week() function  you can count the flags Flag_Visit in set analysis.

Post some sample data if you have.

Regards

Vikas

[CorvoAttano]

Hello Vikasmahajan,

Thank you for your suggest!
I already have a master calendar in the data model, but unfortunately my issue here is the for loop function in the script. Since I am scripting at the moment set analysis is not an option, as far as I know.

So far I am sure that I need a for loop, because everything has to happen in the script. If not with a for loop, I do not know how to differ between the frequencies in one function.

[CorvoAttano]

One of my issues that I do not understand is how to tell if the sales person was not there at a customer for 3 weeks in a row.

[vikasmahajan]

post sample data

[CorvoAttano]

Hello,

I cannot post sample data, because I am working on a VPN and I cannot copy or download any files here. On my own desktop I am not able to replicate that since I have there the same conditions. Therefore I try to give an example:

CustomerNo, Frequency, VisitedOn, MadeSale
123456, 1, 01.03.2021, Yes

123456, 1, 12.03.2021, Yes

789789, 3, 01.03.2021, Yes

789789, 3, 10.03.2021, Yes

123789, 2, 01.03.2021, Yes

123789, 2, 08.03.2021, Yes

123123, 5, 01.03.2021, Yes

123123, 5, 30.03.2021, Yes

Again the Frequency-Definitions:

1 = 1x per week

2 = Every two weeks

3 = Every three weeks

4 = Every four weeks

5 = twice a week

So referring to the requirement:
Customer 123456 has to be highlighted, because he has to be visited every Week at least once and the number of work days is greater than 5 between the visits.

Customer 789789 is fine, because the days between the visits are less than/ equal three weeks.

Customer 123789 is fine, because he has to be visited at least once in two weeks and that happened.

Customer 123123 has to be highlighted, because he has to be visited twice a week, but it has been almost a month since the last visit.

I hope that helps!

Thank you in advance and best regards