10 Replies Latest reply on Jun 12, 2017 5:22 AM by Prajval T

# continues Weekly trend line for all years

Hi Team,

I need to develop a view like below.

meas one measure on bar and other on line against week in each month and liens and bars should be continues.

i tried to do that. But Weeks are coming for entire year. I have to develop the view like exactly above. can any one please help me

• ###### 1. Re: continues Weekly trend line for all years

Hi, Prajval

what's the case one week that couple of days in the end of one month and the rest of the days are in the beginning of next month? How do you count it?

ZZ

• ###### 2. Re: continues Weekly trend line for all years

Hi Praval--

If you haven't already, I'd do some Googling & checking on Tableau forums... I suspect someone has asked a similar question.

I found some hopeful leads here:

I think the primary trick here is to get a calculation that will give you the week of the month. And while this could be quite simple, there are a number of questions you need to ask yourself:

• Is the week number simply a function of the day? That means Week 1 will always be days {1,2,3,4,5,6}, but that the starting day of the week might vary.
• Is the week number a function of the weekday? This means Week 1 might be Monday-Sunday, and it also means a month's week might overlap two months
• Are you worried about months with five weeks or incomplete weeks?
• What happens when the calendar changes (leap year)... will that affect things?

One way you could solve this is to create a second file that list the appropriate week # next to every date, and blend these together as a "scaffold."

Another way (a quick way, in fact) is to calculate the week number within the month. Note: this is when things are a function of the day:

Once you have this, you should be able to break things down by Month/Year and Week Number.

You may need to make some changes, though, based on how you want to handle Week 5 (without knowing how you plan to tackle that in your calcs, I can't help you out right here).

Good luck to you!

--Michael

• ###### 3. Re: continues Weekly trend line for all years

Thank you Michael and ZZ for the reply.

i used the below calculation to get the week numbers.

DATEPART('week',[Order Date]) - DATEPART('week',DATETRUNC('month',[Order Date]))+1.

But if i have month like below i should get 6 weeks. is that possible to get 6 weeks for this kind of months??

i tried to get the my requirement, i almost reached. But one issue is pending.

PFB screen shot, highlighted at red color. the line is breaking there. it should go continuously with out breaking(Please refer my initial post).

below are my specific requirements.

1. Can we avoid the break in the line, i need continuous flow.

2. in the weekly report, can bring down the month and year down to the week labels.

• ###### 4. Re: continues Weekly trend line for all years

Hi, Prajval

Is this what you are after?

compare to yours before

I attached the updated workbook with 10.2, I don't have 9.3 version.

If you have any other question, please let me know

ZZ

• ###### 5. Re: continues Weekly trend line for all years

Hi Zz,

Thank you so much for the reply.

But i am not able ot open the workbook.  i am using 9.3 verison. Can you please send the work book with 9.3.

Regards,

Prajwal.

• ###### 6. Re: continues Weekly trend line for all years

Hi ZZ,

i have verified the attached workbook.

you showed monthly data. i need to show the Weeks numbers like exactly below.

One more issue is if you see below screen shot which i tried, some weeks are missing on the label as we didnt have data for those weeks in respective months. But my requirement is, i have to show the week numbers even though data is not there.

Is it possible?

• ###### 7. Re: continues Weekly trend line for all years

Hi, Prajval

Don't worry about the monthly, look at the weekly worksheet

I created a calculation field [Modified_date] which is doing the same way as you put generate your week number.

Since you want show the continuous line, so it has to be a continue value of date.

The axis didn't show the week number either, but instead, it shows either first day of your week number.

Below is the snapshot of calculation code.

If you look at the dashboard, you will find the changes as shown below

Please let me know if you still have question.

ZZ

• ###### 8. Re: continues Weekly trend line for all years

HI ZZ,

I understood that, you are showing first day of the week. But i need to show the label like Week 1, Week 2 etc with in each month like below.

Is there any way to show weeks like above.

Regards,

Prajwal

• ###### 9. Re: continues Weekly trend line for all years

Hi, Prajval

Please find below snapshot of how to display the week number instead of dates in the axis. It is not exactly the same as your sample, but I think that the best Tableau could do as you want the line display as continuous line.

Workbook attached with update weekly sheet. Please let me know if you have question

ZZ

• ###### 10. Re: continues Weekly trend line for all years

Thank you so much ZZ for your effort.

It helps me to my requirement.

Regards,

Prajwal