Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
There is data like below. How to count consecutive occurrence of a store by month in Script.
Store ID | Year Month | Consecutive Occurrence |
A | 202101 | 1 |
A | 202102 | 2 |
A | 202103 | 3 |
A | 202104 | 4 |
A | 202106 | 1 |
A | 202107 | 2 |
B | 202101 | 1 |
B | 202102 | 2 |
B | 202107 | 1 |
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:
Make sure, store ID & Date are in asc order
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:
Make sure, store ID & Date are in asc order
Thanks Mayil!
Will it also work if I have sub store for a store . Like below
StoreID | Sub Store ID | YearMonth | Consq_Occurr |
A | A1 | 202101 | 1 |
A | A2 | 202101 | 1 |
A | A3 | 202101 | 1 |
A | A4 | 202101 | 1 |
A | B1 | 202102 | 2 |
A | B2 | 202102 | 2 |
A | B3 | 202102 | 2 |
A | B4 | 202102 | 2 |
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
State | Wrong Deployment | TVA Drop | WD Occur for 1 month | WD Occur for 2 month |
California | 2249 | 364 | 1230 | 800 |
Texas | 1419 | 239 | 800 | 600 |
New York | 800 | 194 | 200 | 50 |
Illinois | 463 | 129 | 100 | 50 |
Pennsylvania | 719 | 124 | 500 | 200 |
HI
Can you explain the issue in details? How you get the 1230 for California?
Please provide sample data and expected output