topic Re: Creating buckets based on week no's in QlikView

Creating buckets based on week no's

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

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 Date	Week	Bucket Value//
05/01/2015	1	02/01/2017
04/01/2016	1	02/01/2017
02/01/2017	1	02/01/2017
12/01/2015	2	09/01/2017
11/01/2016	2	09/01/2017
09/01/2017	2	09/01/2017
etc
15/06/2015	24	12/06/2017
13/06/2016	24	12/06/2017
12/06/2017	24	12/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

Re: Creating buckets based on week no's

sunny_talwar — Wed, 10 Jan 2018 18:57:57 GMT

I think what you need is this

The As-Of Table

Re: Creating buckets based on week no's

hopkinsc — Wed, 10 Jan 2018 19:19:36 GMT

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?

Re: Creating buckets based on week no's

sunny_talwar — Wed, 10 Jan 2018 19:26:28 GMT

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....

Re: Creating buckets based on week no's

hopkinsc — Wed, 10 Jan 2018 19:34:10 GMT

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

Re: Creating buckets based on week no's

sunny_talwar — Wed, 10 Jan 2018 19:38:41 GMT

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

Re: Creating buckets based on week no's

hopkinsc — Wed, 10 Jan 2018 20:25:31 GMT

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

Re: Creating buckets based on week no's

sunny_talwar — Wed, 10 Jan 2018 20:28:31 GMT

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

Re: Creating buckets based on week no's

hopkinsc — Thu, 11 Jan 2018 14:29:39 GMT

can anyone help with this please?

Re: Creating buckets based on week no's

sunny_talwar — Thu, 11 Jan 2018 15:08:47 GMT

Check the attached script

Re: Creating buckets based on week no's

hopkinsc — Thu, 11 Jan 2018 16:06:40 GMT

Hi Sunny,

thanks for this, i can see its nearly there.

when no selections are made its obviously going to show all years, i only want current and prior so i have created 2 expressions, one for current and one for prior but i am still getting a mixture of years on the x axis.

what i want is for no selections to be made, the chart to show current and prior, on this example, the x axis should start with the date 17/10/2016 and go to 09/10/2017 - which is all current fiscal year (Current Year (MAT))

but the chart should still show 2 lines, but both years using the dates from the current year..

Re: Creating buckets based on week no's

sunny_talwar — Thu, 11 Jan 2018 16:15:04 GMT

Created a flag in the script

Now for current year use Flag = {'0'} and for previous year use Flag = {'-1'}

Re: Creating buckets based on week no's

hopkinsc — Thu, 11 Jan 2018 16:30:08 GMT

Hi Sunny, im still only getting 2017 dates on the x axis.. these are the dates that i need on the x axix,

but what i'm getting is...

so the circled dates in the top pic need to be the circled dates in the bottom pic.

if you select 'current year (MAT)' in the straight table you will see the date range that i need on the x axis.

this is an awkward one i think!

Re: Creating buckets based on week no's

sunny_talwar — Thu, 11 Jan 2018 16:39:23 GMT

I think you never unselected the AsOfYear Field which was selected to show 2017

Re: Creating buckets based on week no's

hopkinsc — Thu, 11 Jan 2018 16:42:40 GMT

i didn't notice that selection until after i posted the last message, but when unselecting i get 2015 dates also in the chart

Re: Creating buckets based on week no's

sunny_talwar — Thu, 11 Jan 2018 16:49:54 GMT

You want which years?

Re: Creating buckets based on week no's

hopkinsc — Thu, 11 Jan 2018 16:58:00 GMT

it should end with the most up to date data. so in this example it would be up to 09/10/2017 and then go back 52 weeks, meaning the 1st week will be 17/10/2016.

so its that date range that would make the buckets.

the x axis would start at 17/10/2016 which is week 42, so that date would also be the x axis value for the same week in the prior year. so against 17/10/2016 it would show the data for current year week 42 (current is current year (MAT)) and also data for week 42 prior year (Prior Year (MAT))

i think i may have confused you! this is confusing stuff

Re: Creating buckets based on week no's

hopkinsc — Thu, 11 Jan 2018 17:52:47 GMT

maybe another way.

all i want is to show dates instead of week numbers. at the moment the week numbers work as i want, so is there an easy way i can just change the label values?

i.e.

with the current example, the x axis goes from week 42 to week 41 in the current year (MAT). so week 41 relates to the Max({<MAT_Year = {'Current Year (MAT)'}>}Calendar_Date) - which return 09/10/2017. so can i replace that value to the last value on the x axis then deduct 7 each time from the previous week number values on the x axis?

so to hardcode it would be..

if(CALENDAR_WEEK=41,'09/10/2017')

obviously thats hard coding, but i dont know how to go about it dynamically.

is that a possibility?

Re: Creating buckets based on week no's

sunny_talwar — Thu, 11 Jan 2018 18:15:10 GMT

Check attached

Re: Creating buckets based on week no's

hopkinsc — Thu, 11 Jan 2018 18:22:09 GMT

you know what.... that looks like its it!!

thanks so much, i'll study the scripting now to try and understand what you have done.

time to implement it inot the live version now and hope it works