6 Replies Latest reply on Oct 8, 2016 10:10 PM by Sandeep Kotha

# Week Calculation field Problem??

Hi All,

I have a question on Week Calculation field.

I have Below fields :

1. Month: It consists of few months data.

2.Week:    It consists of Week  numbers 1,2,3,4 or 5. Every month either have 4 or 5 weeks.Based on the respective month,it would have either 4 or 5 as the maximum value.

3.Sales : It has the respective Week Sales Value.

My question is that:

I want to create a calculation field as "Sales based on on max(Week)" so that when I click any month, it should give the respective maximum week Sales value.

I have written A calculation field as below.But it is not giving as per my requirement.

If [Week]={fixed:max([Week])} then Sales else 0

When I apply this formula, it is giving value as :

Whichever month has 5 weeks ,it is giving corresponding sales value and the rest of the months as zero sales value.

I have attached the workbook for reference.In the workbook,only August is giving values and the rest is giving as zero values.

Best Wishes,

Sandeep

• ###### 1. Re: Week Calculation field Problem??

Put context filter on your [Fiscal Month]. In calculations Context filter applies before your LOD. Hope this help.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Week Calculation field Problem??

Hi Sandeep,

write the below calculation

IF [Week] = { FIXED [Fiscal Month] : MAX([Week])} THEN [Sales] END

please check the attached workbook Result sheet.

Thanks,

Sreekanth.

1 of 1 people found this helpful
• ###### 3. Re: Week Calculation field Problem??

Thanks Sreekanth. It worked for me.

Thanks again:)

• ###### 4. Re: Week Calculation field Problem??

Hi Mahfooj,

It worked for me.Thank you very much for the help. What exactly Context filter is ? Can u pls brief about it?

• ###### 5. Re: Week Calculation field Problem??

"By default, all filters that you set in Tableau are computed independently. That is, each filter accesses all rows in your data source without regard to other filters. However, you can set one or more categorical filters as context filters for the view. You can think of a context filter as being an independent filter. Any other filters that you set are defined as dependent filters because they process only the data that passes through the context filter."

This is now much more relevant because in 9.0 LOD expressions are applied at different points in the filter order of execution:Tableau's Order of Operations

When we use a FIXED LOD calc, the expression is calculated after the Extract, Data Source & Context filters, but before the Dimension, Measure & Table Calc filters. This let's us do things like calculate a percent of total without having to use Table Calcs, as we use to do.

But there might be cases where you want some Dimension filters to effect a FIXED LOD. By adding the Context to a Dimension filter you move it up in the order of execution ahead of the FIXED LOD.

Beware of using lots of Context filters as they can slow down performance If you're connected with a huge data source then putting context on any dimension can take some time. But once done it will be efficient.

Mahfooj

• ###### 6. Re: Week Calculation field Problem??

Hi Mahfooj,