Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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.
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;