Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
lashagoch
Contributor II
Contributor II

Year over year change by week on line chart Qlik Sense

Hi all,

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

yearweek_startindex_w
201905/05/201939
201912/05/201945
201919/05/201950
201926/05/201939
201902/06/201939

 

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

lashagoch_0-1619748588021.png

 

 

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

2 Solutions

Accepted Solutions
Digvijay_Singh
Master III
Master III

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.

 

View solution in original post

lashagoch
Contributor II
Contributor II
Author

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;

View solution in original post

2 Replies
Digvijay_Singh
Master III
Master III

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.

 

View solution in original post

lashagoch
Contributor II
Contributor II
Author

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;

View solution in original post