Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Creating buckets based on week no's

Hi all,

i have a requirement that i'm unsure of how to do it.

Basically i have some data for current and previous year. i want to create buckets with the week commencing dates of the current year as the values but each bucket would be linked to the corresponding week of previous years.

so for example

W/C DateWeekBucket Value//
05/01/2015102/01/2017
04/01/2016102/01/2017
02/01/2017102/01/2017
12/01/2015209/01/2017
11/01/2016209/01/2017
09/01/2017209/01/2017
etc
15/06/20152412/06/2017
13/06/20162412/06/2017
12/06/20172412/06/2017
etc

so i want a bucket for each w/c date of the current year but each bucket would have the corresponding week from previous years too.

i think i only need current and previous, not -2 years, -3 years etc. so currently 2017 and previous 2016

sorry, this is a difficult one to explain!

thanks

1 Solution

Accepted Solutions
sunny_talwar

22 Replies
sunny_talwar

I think what you need is this

The As-Of Table

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

hmm i dont see how that would work unless i'm missing something.

the result i am after is the chart as is in the example i attached but instead of having the week numbers as the x axis i want the current years week commencing dates.

the chart has a line for current year and previous year, the previous year week commencing dates will be  a day out to the current year dates, but i want them to fall against the current years dates.

not sure if that makes it more clear?

sunny_talwar

I don't have the files to reload your dashboard... but with as of table you can link this year and current year's week to this years week start date....

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

ahh ok, i'll have a go at that now..

sunny_talwar

The idea with a very basic example is this

Fact Table

Year     Sales

2017     102

2016     120

2015      130

AsOfTable

AsOfYear     Year     Flag

2017               2017     CY

2017               2016     PY

2016               2016     CY

2016               2015     PY

2015               2015     CY

Now when you use AsOfYear as your dimension instead of Year and use this expression

Sum({<Flag = {'CY'}>}Sales) and Sum({<Flag = {'PY'}>}Sales)

You will see 2017 to show 2017 numbers with first expression and 2016 numbers for the second expression

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

I'm confused! its the week numbers and dates that i am struggling to get my head around!

are you able to help?

data attached..

no worries if you're busy, i'll keep trying

sunny_talwar

If nobody is able to help by tomorrow... I will try to pick it up again... almost end of day for me

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

can anyone help with this please?

sunny_talwar

Check the attached script

Capture.PNG