Year over year change by week on line chart Qlik Sense

[lashagoch]

Hi all,

I have last 2 years weekly Google Trends data with this format:

year	week_start	index_w
2019	05/05/2019	39
2019	12/05/2019	45
2019	19/05/2019	50
2019	26/05/2019	39
2019	02/06/2019	39

I would like to get Y-Y % change of index_w by week on a line chart like the below graph:

I have tried many formulas included Above/Below however the week_start format complicates things. Do you have any advice on how to get a Y-Y % change to put on the line chart?

Many thanks in advance.

Lasha

[Digvijay_Singh · Accepted solution]

I think you can only get Y-Y % weekly change if you got matching weeks data for each year, the sample datta you shared are not showing data for each month/week for an year. I see missing months in 2019. If your real data have all weeks data, Is it possible if you create new WeekID field and assign it same ID for different year? 

Something like this - 

temp:
LOAD
"year",
"date",

index_w
FROM [lib://AttachedFiles/input_sample.xlsx]
(ooxml, embedded labels, table is WEEKLY);

Final:
Load *,
if(year=Previous(year),peek(WeekID)+1,1) as WeekID
resident temp
Order By year
;

drop table temp;

This might help to create comparison expression but not sure if your data can use this trick or not.

[lashagoch · Accepted solution]

Thank you, Digvijay for taking the time and providing great tips!  Indeed the issue was with Weeks.

My colleague just find out the solution:

[WeekCompare]:

load
year(date(date)) As year,
Week(date(date)) AS Week,
avg(index_w) as CurrentValue
resident [MyTable]
group by
year(date(date)),
Week(date(date));

left join

load
year(AddYears(date(date),1)) As year,
Week(date(date)) AS Week,
avg(fact_index_wk) as HistValue
resident [MyTable]
group by
year(AddYears(date(date),1)),
Week(date(date));

[FinalData]:
NoConcatenate

load year,
Week,
CurrentValue/HistValue as RatioResult
resident [WeekCompare]

where not isnull(HistValue);

drop table WeekCompare;