Looping Over Dates and Five star center in Qlikview

[shraddha6369]

Hi All,

I have a requirement where I have to find count of members who visited five star centers. Here is a requirement in detail:

Case 1: Suppose a member(John) visits in Jan 2020 to a five star center. so for next 5 months he is considered as unique member (Jan2020 to May 2020, flag for unique member should be set to 1). 

Case 2: Part A) Suppose John visits in Jan 2020, so we started setting the flag for next 5 months to one, but he visits the same five star center in March 2020, so now the flag counter should be reset and we need to calculate 5 months from March now (so Instead from Jan 2020-May2020, we need flag to be set to 1 from March 2020 to Aug 2020 ). Similarly if he visits again before August 2020, flag should set again for next 5 months. 

Past B) If he visits in Oct 2020 then Sept month should not have that flag as 1 but again from Oct 2020 to Feb 2021 flag is set to 1.

This was all for one five star centers. I have multiple five star centers in my data.

I am creating a sample data but till then if any one can suggest some logic to implement this , it will be helpful.

Thank you! 

[edwin · Accepted solution]

i would get the distinct dates per member where the center is 5 star.  these could be in span of over 5 months or less it doesnt matter
then do a while iterno() from 1 to 5, add additional rows using the date: addmonths(DateVisited+iterno())  this way you get at least 1 row where the member is eligible for count:

if member visits Jan and Mar and Oct:
for Jan visit: Jan, Feb, Mar, Apr, May
for Mar: Mar, Apr,May,Jun,Jul
for Oct: Oct, Nov, Dec, Jan, Feb
then you can get the distinct: Jan, Feb, Mar, Apr, MayJun,Jul, Oct, Nov, Dec, Jan, Feb

[edwin]

correction, i think it should have been addmonths(DateVisited,iterno()-1) to include the date visited + 4 moths after that

[edwin]

or iterno() from 0 to 4

[shraddha6369]

Hi ! Thank you much! This helped me to get what I needed. I appreciate your help.

[edwin]

yw