Creating buckets based on week no's

[hopkinsc]

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

[hopkinsc]

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

[sunny_talwar]

Created a flag in the script

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

[hopkinsc]

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!

[sunny_talwar]

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

[hopkinsc]

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

[sunny_talwar]

You want which years?

[hopkinsc]

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

[hopkinsc]

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?

[sunny_talwar · Accepted solution]

Check attached

[hopkinsc]

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