Calculate the Number of Consecutive Weeks of WoW Declines in Sales

[nbuchholz1]

I'm trying to count the number of consecutive weeks that the Week on Week change in Sales is < 0 (basically the number of weeks in a row that sales have declined). I think this should be done in the load script, but I'm not entirely sure how to approach!

My data table would look something like this: 

Week	Year	Sum of sales
41	2020	1000
40	2020	129
39	2020	477823
38	2020	78324
37	2020	7378
36	2020	27834
35	2020	38783
34	2020	287812
33	2020	237878
32	2020	237878
31	2020	238
30	2020	4637
29	2020	834728
28	2020	6738
27	2020	23478
26	2020	24878
25	2020	2767
24	2020	59477
23	2020	34783
22	2020	7348
21	2020	5789

Thanks in advance for any input

[Taoufiq_Zarra]

@nbuchholz1  can you also share the output expected from this sample ?

[nbuchholz1 · Accepted solution]

@Taoufiq_Zarra  The output would be something like this: 

Week	Year	Sum of Reads	Change from Week Prior	Is Negative Decline	Consecutive Weeks Of Sales Decline
41	2020	1000	675%	0	0
40	2020	129	-100%	1	1
39	2020	477823	510%	0	0
38	2020	78324	962%	0	0
37	2020	7378	-73%	1	3
36	2020	27834	-28%	1	2
35	2020	38783	-87%	1	1
34	2020	287812	21%	0	0
33	2020	237878	0%	1	1
32	2020	237878	99849%	0	0
31	2020	238	-95%	1	2
30	2020	4637	-99%	1	1
29	2020	834728	12288%	0	0
28	2020	6738	-71%	1	2
27	2020	23478	-6%	1	1
26	2020	24878	799%	0	0
25	2020	2767	-95%	1	1
24	2020	59477	71%	0	0
23	2020	34783	373%	0	0
22	2020	7348	27%	0	0
21	2020	5789	-	0

I'm pretty clear how I would address this if I just needed to build a table of data going forward.  However, the challenge is I need to run this analysis on my historical data. I believe I somehow need to iterate through the whole time series to come up with these calculations, but I'm not sure how to do that in Qlik. 

[Taoufiq_Zarra · Accepted solution]

@nbuchholz1  Maye be this:

Data:
LOAD * INLINE [
    Week, Year, Sum of sales
    41, 2020, 1000
    40, 2020, 129
    39, 2020, 477823
    38, 2020, 78324
    37, 2020, 7378
    36, 2020, 27834
    35, 2020, 38783
    34, 2020, 287812
    33, 2020, 237878
    32, 2020, 237878
    31, 2020, 238
    30, 2020, 4637
    29, 2020, 834728
    28, 2020, 6738
    27, 2020, 23478
    26, 2020, 24878
    25, 2020, 2767
    24, 2020, 59477
    23, 2020, 34783
    22, 2020, 7348
    21, 2020, 5789
];

Tmp:
noconcatenate

load * resident Data order by Year,Week;

drop table Data;


Final:
noconcatenate

load *,if([Is Negative Decline]='1' and peek([Is Negative Decline])=[Is Negative Decline],peek([Consecutive Weeks Of Sales Decline])+1,[Is Negative Decline]) as [Consecutive Weeks Of Sales Decline];

load *,Num(if(Year=peek(Year),([Sum of sales]-peek([Sum of sales]))/peek([Sum of sales])),'# ##0%') as [Change from Week Prior]
,if(Num(if(Year=peek(Year),([Sum of sales]-peek([Sum of sales]))/peek([Sum of sales])),'# ##0%')<0,'1','0') as [Is Negative Decline]

  resident Tmp;

drop table Tmp;

output:

[Gabriel · Accepted solution]

@nbuchholz1 

Please see script below and hopefully it helps you.

tmp:
LOAD
"Week",
"Year",
"Sum of sales"
FROM [lib://CommunityQuestion]
(html, utf8, embedded labels, table is @1);

NOCONCATENATE
tmp1:
LOAD * RESIDENT tmp ORDER BY "Year","Week" ASC;

DROP TABLE tmp;

NOCONCATENATE

tmp2:
LOAD *,
IF("Week" > PEEK("Week") AND [WoW Changes] < PEEK([WoW Changes]),1,0)
AS [Consecutive_Decline]
;
LOAD *,
(("Sum of sales" / PEEK("Sum of sales"))-1) AS [WoW Changes],
IF((("Sum of sales" / PEEK("Sum of sales"))-1) <0, 1 , 0) AS [Flag_Negative_Decline]
RESIDENT tmp1;

DROP TABLE tmp1;