9 Replies Latest reply on Jun 7, 2018 8:25 PM by Sheng Zhong

# LOD Fixed question

Hi, Tableau Helper

I have a question about LOD fixed. Here is a screen shot:

Test1 is calculated field: However, Test another calculated field involved with LOD does not work as what I expect. Ideally it should sum all test1's values to 3 instead of 1 currently. I try to use window_sum, it does work but it prevent me from later calculation because I can not use window_sum within another window_sum like. So I have to apply LOD to this calculated field, but why does it so far yield 1 ? where is wrong on my formula?  I have attached twbx with this message. Thanks for your help! • ###### 1. Re: LOD Fixed question

It yields one because test 1 has either a value of 0 or 1.  If you fix test1 at the supplierID level it will still have a maximum value of 1.

• ###### 2. Re: LOD Fixed question

Hi, Ivan and keep test unchanged. But it still does not yield the value I want. Ideally "test" should yield 3

• ###### 3. Re: LOD Fixed question

Hi Sheng,

Give this a try,

1. Create a custom date for Period.  Select Months and Date value.

2. Alter test1 to { FIXED [Supplier], [Period (Months)] : max(IIF(year([Period])=year([End Date])and (MONTH([End Date]))-[Comparison  Period]<=month([Period]) and MONTH([Period])<=MONTH([End Date]),1,0)) }

3.  Alter test to {FIXED [Supplier]: SUM([Test1])} Let me know if it works for.

Regards,
Ivan

• ###### 5. Re: LOD Fixed question

Hi, Ivan

Yes it works. Thanks for your help! But I just cannot think through logically. for "Test1", why I need to fix on yearly and monthly level in order to achieve that?

• ###### 6. Re: LOD Fixed question

Yes it works. Thanks for your help. But why I need to fix on yearly and monthly level in order to achieve that?

• ###### 7. Re: LOD Fixed question

Your Test1 returns values like that and Test has to be an aggregation of Test1 per Company.

• ###### 8. Re: LOD Fixed question

Hi Sheng,

I'll do my best to explain although it's a little tough to articulate

First I'm going to nest both expressions

{FIXED [Supplier]: SUM({ FIXED [Supplier] : max(IIF(year([Period])=year([End Date])and (MONTH([End Date]))-[Comparison  Period]<=month([Period]) and MONTH([Period])<=MONTH([End Date]),1,0)) })}

The inner expression representing test1 will be calculated first and the expression will return single value of 1 when fixed at the supplier level.

The outer expression will then be evaluated which is {FIXED [Supplier]: SUM(1) }

What you are trying to do in Test is count the number of months where the following is true which is why you need to add period months to your view.

year([Period])=year([End Date])and (MONTH([End Date]))-[Comparison  Period]<=month([Period]) and MONTH([Period])<=MONTH([End Date].I c

It does take a little while to wrap your head around how LODs work.  LODs work a bit like a group by in SQL which you join back to your primary source.  In fact I used SQL to replicate LOD functionality before 9.0.

If you are a good with SQL I could try to explain it in those terms.

Regards,

Ivan