Set Analysis on Week Start Date

[#1]

In the chart below I am using Set Analysis to compare the trips/week year over year. How can I get the chart to show actual dates of the first of the week, instead of the week numbers (seeing as how most people can't look at a week number and know the week start date). It seems the Set Analysis will only work if I use week numbers. 😞

[#2]

You calculate the week in the script? Then add a field with the first date of week (function weekstart).

Use your Set Analysis and change the dimension to the data.

[#3]

Thanks for the response.

I do calculate the week start in the script, but the a week start date will be Dec. 6 in 2009, and Dec 7. in 2008, which means that since the week start dates are different, the set analysis will read them differently and will not be able to stack them. Does that make sense? If I use the week number then the set analysis will read it as 49 in 2008 and compare it to the same week 49 in 2009. Any other suggestions?

[#4]

Which start date would you be displaying? If they are different for each year, then you have to choose which one to display.

Some of the data I work with is similar. We want to compare year to year by week, but the actual dates are different. We have a calendar table that has a field which stores the corresponding date in the previous year. I think by utilizing a calendar table, you could come up with a way to match the years up.

One idea would be to load up the week numbers and the 2009 (current year) start dates as a separate table. Then link up the week numbers from the main data set to this week numbers in the new table. Then for each record, you have a week number and corresponding current year week start date.

[#5]

I would be displaying the current year's week start date and always comparing it to last years.

I came up with this formula, but I can't get it to work. Can you tell where I'm going wrong? I am trying to use dollar sign expansion to take the pickupweek & year (39-2009), trim it to 39-20, then take the far right 2 numbers and subtract one, leaving me with 39-2008 which is the set I'm looking for.

count({$<PickupWeekYear = {$(#=left(Only(PickupWeekYear),len(PickupWeekYear)-2) & (right(Only(PickupWeekYear),2)-1))}>} TripNo)

[#6]

Try putting this expression into a table, but don't give it a label. Then when the chart is rendered, you will see the Set Analysis expression with the dollar sign expansion evaluated for your label. That should tell you right away if your dollar sign expansion is working.

Since you're using ONLYs, you can probably throw the dollar sign expansion into a Text Object and see if it evaluates to what you are looking for.

One quick guess is that you will need quotes around the dollar sign expansion:

count({$<PickupWeekYear = {"$(#=left(Only(PickupWeekYear),len(PickupWeekYear)-2) &
(right(Only(PickupWeekYear),2)-1))"}>} TripNo)

[#7 · Accepted solution]

Daron,

How about something like I've attached? Use the week number to get the year over year comparison with a simple expression, but use a dual to label the week number with the start date of the week using the current year.

Tom

[#8]

Thanks everybody for your help. Right now it looks like Tom's solution will work best. I'll have to check back when I'm close to completing the project to make sure it will still work, but as for now it's working like a charm!

[#9]

Nice & very useful example .

[Anonymous]

I have a question I have a field number of weeks, and I want to select and show me 8 weeks numbers backwards

is it possible?