topic week-year comparison over 3 years in QlikView

week-year comparison over 3 years

Wed, 19 Oct 2016 13:45:43 GMT

I need to create a line chart that has a week-year dimension and calculates the yoy % growth at each point.

I have data that spans 3 years, so i'm struggling to use set analysis to do this.

any suggestions?

i.e at wk1 2016 growth from wk1 2015, at wk1 2017 growth from wk1 2016 etc

Re: week-year comparison over 3 years

sunny_talwar — Wed, 19 Oct 2016 19:13:35 GMT

You can consider using the The As-Of Table‌ approach

Re: week-year comparison over 3 years

Wed, 19 Oct 2016 19:58:53 GMT

Thanks Sunny, I've used asof before but to get a cumulative value where using the cumulative tick box doesn't work with distinct values on a chart.

I'm not quite clear how I could use it here though.

I'd like my line chart/straight table to show for the week year as a dimension so for 012016 the % YOY difference to only 012015 not the full year between those dates. The 2nd marker 022016 sowing YOY difference to 022015 etc.

I can't figure out how to write the expression even using variables so end up with two years worth of data on my chart.

thank you for your reply, apologies if i'm missing the obvious!

Re: week-year comparison over 3 years

sunny_talwar — Wed, 19 Oct 2016 20:00:37 GMT

I won't understand the whole scenario unless I see a sample. Would you be able to share a sample to show what you have and may be we can offer a better suggestion?

Re: week-year comparison over 3 years

Fri, 21 Oct 2016 09:28:10 GMT

Hi Sunny

After chatting to a colleague about AsOf that is the solution I've used.

I've combined the year and week and used AsOf to add a CW and PW (current and previous week) to my calendar.

This has allowed me to write my expression;

= (sum({<Group={'One'},WeekType={'CW'}>}Customers)

- sum({<Group={'One'},WeekType={'PW'}>}Customer))

/ sum({<Group={'One'},WeekType={'PW'}>}Customer)

plotting against the dimension AsOfYearWeek

Re: week-year comparison over 3 years

sunny_talwar — Fri, 21 Oct 2016 15:56:28 GMT

Awesome!!!

I am glad you managed to figure it out . I would request you to close this thread by marking your own response as the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny