1 2 Previous Next 16 Replies Latest reply on Dec 3, 2016 4:48 AM by Yingying Zeng

# Create the monthly YoY based on the numbers on the last week of the month

I want to have a YOY growth bar chart as above but do want to use the numbers from the last weekend date in a month only as show below in the red frame  instead of  all the cntPosOpe number in a month together. How can I do it? Thanks,

Message was edited by: Yingying Zeng Add the excel datasheet on Dec 3 2016 for the latest version

• ###### 1. Re: Create the monthly YoY based on the numbers on the last week of the month

Hello this can be accomplished with an LOD calculation - fixing the maximum week for each Month Year combination.

I broke the calculations down, but you could do it in less:

Here is a link to the workbook on tableau public so you can reverse engineer.

1 of 1 people found this helpful
• ###### 2. Re: Create the monthly YoY based on the numbers on the last week of the month

Hi Yingying,

Find my approach as reference below and stored in attached workbook version 9.3

1 of 1 people found this helpful
• ###### 3. Re: Create the monthly YoY based on the numbers on the last week of the month

Hello Ryan, thank you so much for the reply. I was wondering how can I have a window function where there is no window table I the real YOY table. It seems that LOD is the key.

• ###### 4. Re: Create the monthly YoY based on the numbers on the last week of the month

Thanks Norbert for always providing the twbx worksheet and making things very straightforward.

Could you please kindly advise whether I do need the date fields in the red frame( I do not have the fields in my data source)  for the dimension of LOD or can we get other replacement such as Date(Week Numbers) in orange frame ?

Thanks again

• ###### 5. Re: Create the monthly YoY based on the numbers on the last week of the month

Hi Yingying,

Thanks for reaching out. I have other obligations at this moment but will come back to you a.s.a.p.

Have a nice one.

• ###### 6. Re: Create the monthly YoY based on the numbers on the last week of the month

The only thing I can think of is that I created in a more recent version of Tableau!

The LOD was the same as Norbert's, just broken out into date parts rather than date values. His solution is more elegant!

I can still perhaps help, are you saying the date field highlighted in red is not in your actual data set? What does your actual date field look like?

1 of 1 people found this helpful
• ###### 7. Re: Create the monthly YoY based on the numbers on the last week of the month

Got it. Thanks Ryan. The actual Date only have one date column which is the one highlight in the orange, which is calculated by ETL developer . I do not know  whether I should use the same column as the dimension in LOD or we need bring other date column in?

I also want to know what does "Date(Month/Year) " in red frame represent, to fix the dimension in the month level?

• ###### 8. Re: Create the monthly YoY based on the numbers on the last week of the month

Hi Ryan,

Thanks for stepping in. Appreciated:)

Have a nice one.

• ###### 9. Re: Create the monthly YoY based on the numbers on the last week of the month

Understand. Take your time and thank you so much for always helping me!!!

• ###### 10. Re: Create the monthly YoY based on the numbers on the last week of the month

Goodmorning Yingying,

Not sure if I understand your question. The following. May be you are already aware but I will start from "scratch".

The only fields which is coming from the underlying datasource. Fields like "Date (Month/Year)" and "Date (Week numbers)" are based on Date but configured on the, what I find, very rich date functions in tableau.

Based on Date you can create Custom Date

Date Functions in Tableau are explained here

Hope it helps. Let me know if you need more clarification.

• ###### 11. Re: Create the monthly YoY based on the numbers on the last week of the month

Hello Norbert,

Thanks again for taking time to reply to me

Yes I got the idea that how the data function works and I knew that you were using the fake data.

My problem that I do not have the  fields named Date in ( which has all the continuous date from 20XX- Jan-01, 20XX-Jan-02)  in my data set, which you used as the dimension for LOD but only have the date field, which is similar to the fields Date(week numbers) in your date set.

We can not use the Create Custom Date on a calculated field(Date(week numbers)) ,right?

{fixed [Date (Month / Year)]: max([Date (Week numbers)])}

• ###### 12. Re: Create the monthly YoY based on the numbers on the last week of the month

Goodevening Yingying,

Can you describe exactly which date-fields are available in your datasource?

• ###### 13. Re: Create the monthly YoY based on the numbers on the last week of the month

Yes. The date field in the data source has  be redefined to be the end of the week by function.

For example :

When the original  date is 2016/11/28 , the re-defined date will be 2016/12/2 ( the end of the week)

When the original date is 2016/12/2, the re-defined date will be 2016/12/2( the end of the week)

All the date has been replaced by the end of the week. I do not remember that whether tableau default the Saturday as the week of the end but we manually do it in the data source.

Let me know if you want to see more data sample.

Thanks so much for the help

• ###### 14. Re: Create the monthly YoY based on the numbers on the last week of the month

Goodmorning Yingying,

Find my approach as reference concerning "maxweek" below and stored in attached workbook version 9.3

1 of 1 people found this helpful
1 2 Previous Next