1 2 Previous Next 22 Replies Latest reply on Nov 23, 2015 3:57 PM by Tina Matt

# Week/Quarter

Hi,

I have attached a sample workbook with some data. The data gets updated every day but with out a day stamp. We just have Week and Quarter.

I am having difficulty getting the Current Partially Completed Week, Last completed week, Quarter To Date fields(QTD). Can you please take a look.

I am looking for a table like the below

2015 Q12015 Q22015 Q32015 Q4
Last Completed Week
QTD

and

CustomerQTDPrevious QtrPrevious Year4 Qtr Average
A
B
C
D

Thank You!

• ###### 1. Re: Week/Quarter

Tina you realize that when you ping specific people the rest of us move on to other questions. Because of this I'm not sure you do yourself any favors by pinging people. Maybe I'm wrong.

--Shawn

• ###### 2. Re: Week/Quarter

Yes, you are correct. I apologize. I will refrain from doing that in the future.

Thanks for pointing it out.

• ###### 3. Re: Week/Quarter

Try this:

Pull Quarter and Week in Column

Measure name in rows

create a calc field:

max([Week])=window_max( max([Week]))

and put it on filter and select true.

Then Create another Cal field for quarterly sales: window_sum(sum([Sales]))

double click it and then compute using Pane (across)

1 of 1 people found this helpful
• ###### 4. Re: Week/Quarter

Thanks puspak Agasti for looking at my question. Your solution works perfectly for Quarter to Date.

I apologize for not being very clear in what "Last Completed Week" meant.

When we look at the workbook, we can see that WEEK 8 is the current week in the current Quarter(2015-Q4). I want the sum of sales of each quarter only till the prior week or the last completed week of the current Quarter ie, WEEK 7.

So sum of sales till the last Completed Week in each Quarter not including the current week (WEEK 8) of the Current Qtr.

Hope this helps ..

Sales2014-Q42015-Q12015-Q22015-Q32015-Q4
Till Last Completed Week
Including Current Week- Quarter to Date
Only the Current Week

I am having a tough time to get all the measure. Can you please help when you have a moment.

• ###### 5. Re: Week/Quarter

Can I assume that report will always have latest months data? If so, this one works just fine.. (Assuming the report is running in Nov and the data file contains Nov Month Data)

Calculated Fields:

calc: window_max(if month(max([Month]))=month(today()) then (max([Week in Qtr])) end)

Filter: [calc]=max([Week in Qtr])

Previous Week: lookup(sum([Sales]),-1)

Sales1: window_sum(sum([Sales]))    (Compute using Week In Qtr)

1 of 1 people found this helpful
• ###### 6. Re: Week/Quarter

Thank you for your time. Appreciate it!

Can you please share the workbook itself.

Also, I replicated the above solution in my scenario, the Quarter to Date and Current Week numbers work but I am still not able to get the 'Sales till Last completed Week'. I have attached an excel sheet with the desired logic that I want to achieve using tableau.

Can you please take a final look at it and let me know how we can get it..

Regards,

Tina.

• ###### 7. Re: Week/Quarter

Can you create another Calculated field:

WINDOW_SUM(sum([Sales]),1-INT( right([calc],1) ),-1)

Calc is the field I had created earlier, you may have named it differently.

What it would do it,

Window_sum(Sum(sales,1-8,-1)  <- 8 is 8th week in this case so it would sum up all the values from Week 1 to 7.

Let me know if it works.

Sorry I don't have permission to attach workbook.

1 of 1 people found this helpful

• ###### 9. Re: Week/Quarter

Puspak,

The solution works perfectly. Thank you so much for your time. I replicated in the sample data that I had attached and it is good.

But when I replicate it on my real data, the filter that we create is not showing any True or False values but is the only value that is present is NULL. Any idea why it would happen. I have just Quarter and Week in Qtr in the column shelf but and when I drag the filter, it shows always null. But, in the sample workbook it works perfectly..

Any idea why it would do that.

• ###### 10. Re: Week/Quarter

Can you try computing using

Table(across) or Week in Qtr?

Pull the filter to filter pan and click ok.

Then from the drop down select compute using table (across), let me know if it works.

1 of 1 people found this helpful
• ###### 11. Re: Week/Quarter

I think I figured what is causing the problem.

'Month' is a string field but when I convert it to month by changing default properties to date, the month values are becoming null

Any idea if there is a work around?

• ###### 12. Re: Week/Quarter

which format is it in?

can you attach a screenshot?

• ###### 14. Re: Week/Quarter

Try creating a Calc Field:

DATE([Month]+"-01")

Thnx

1 of 1 people found this helpful
1 2 Previous Next