Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorva11
Contributor II
Contributor II

Consecutive Occurrence by Month

There is data like below. How to count consecutive occurrence of a store by month in Script.

Store IDYear MonthConsecutive Occurrence
A2021011
A2021022
A2021033
A2021044
A2021061
A2021072
B2021011
B2021022
B2021071
1 Solution

Accepted Solutions
MayilVahanan

Hi 

Try like below

Load *, If(Previous([Store ID]) <> [Store ID], 1, If(Date = AddMonths(Previous(Date),1), Peek('Occur')+1, 1)) as Occur;
LOAD *, MakeDate(Left([Year Month], 4), (Right([Year Month],2))) as Date INLINE [
Store ID, Year Month
A, 202101
A, 202102
A, 202103
A, 202104
A, 202106
A, 202107
B, 202101
B, 202102
B, 202107
];

O/P:

MayilVahanan_0-1627030535539.png

Make sure, store ID & Date are in asc order

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

4 Replies
MayilVahanan

Hi 

Try like below

Load *, If(Previous([Store ID]) <> [Store ID], 1, If(Date = AddMonths(Previous(Date),1), Peek('Occur')+1, 1)) as Occur;
LOAD *, MakeDate(Left([Year Month], 4), (Right([Year Month],2))) as Date INLINE [
Store ID, Year Month
A, 202101
A, 202102
A, 202103
A, 202104
A, 202106
A, 202107
B, 202101
B, 202102
B, 202107
];

O/P:

MayilVahanan_0-1627030535539.png

Make sure, store ID & Date are in asc order

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
apoorva11
Contributor II
Contributor II
Author

Thanks Mayil!

Will it also work if I have sub store for a store . Like below

StoreIDSub Store IDYearMonthConsq_Occurr
AA12021011
AA22021011
AA32021011
AA42021011
AB12021022
AB22021022
AB32021022
AB42021022
apoorva11
Contributor II
Contributor II
Author

If for below chart  we need to add last 2 expressions, which shows consecutive month wise store count where wrong deployment occurs. This is on state level. We used Above function but it is giving me row 1 value of first column in row 2 which is wrong as we need common stores current and last month with wrong deployment state wise.

Also , provide a solution if month can be made flexible so that user can see consecutive month as per his selection.

Thanks

StateWrong DeploymentTVA DropWD Occur for 1 monthWD Occur for 2 month
California22493641230800
Texas1419239800600
New York80019420050
Illinois46312910050
Pennsylvania719124500200
MayilVahanan

HI 

Can you explain the issue in details? How you get the 1230 for California? 

Please provide sample data and expected output

MayilVahanan_0-1628159431425.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.